Intercepted: Analyzing Wiretap Records from the US Courts

Author: Henry Peyronnin

Introduction

Under Title III of The Omnibus Crime Control and Safe Streets Act of 1968 and 18 U.S.C. § 2519, The Administrative Office of the United States Courts (AO) is required to publish annual federal and state records of interception orders for wire, oral, or electronic communications. For each intercept, the AO's annual report includes information on the total cost and duration, the offense under investigation, the jurisdiction, and other data. The full set of variables is listed below.

State
Jurisdiction
AO Number
Judge
Prosecutor
Offense
Intercept Type
Location
Application Date
Original Order (Days)
Number of Extensions
Total Length (Days)
Installation
Number of Days in Operation
Average Intercepts per Day
Number of Persons Intercepted
Number of Incriminating Intercepts
Total Cost in Dollars
Other Than Manpower Cost in Dollars
Arrests
Trials
Motions to Suppress Intercepts
Persons Convicted
Cost Related
Results Related

These data reveal important information about the use of surveillance in criminal investigations. Where are wiretaps used most, and for what types of crimes are they most often deployed? How do wiretap characteristics vary by crime, geography, and time period? What underlying changes in technology, policy, or society might explain these trends? Analyzing wiretap records also allow us to gauge their efficacy of as an investigative tool. How often do wiretaps result in arrests or convictions? Do their benefits outweight their costs? The overarching objective of this report is to answer some of these questions.

Literature Review

The breakup of AT&T in 1982 and Bell Systems later that decade led to a period of renewed competition, growth, and innovation in the telecommunications industry. As the number of telecommunications companies proliferated and new technologies flooded the market, the FBI struggled to keep pace with these new source of complexity. Worried about how this would affect their ability to conduct surveillance, the FBI pursued legislation in Congress that would protect its wiretapping abilities from the effect of potentially disruptive technologies. The FBI's argument in favor of legislative protection rested on a key unstated assumption: that wiretaps are indeed vital tools for law enforcement. Is this assumption indeed valid? In Privacy on the Line, Susan Landau and Whitfield Diffie (2010) attempt to find out by digging into the data on wiretaps. They draw mostly on records from 1988 to 1994, but also include data from 1968 to 2006. Their analysis yields insights into the efficacy of wiretaps as well as other aspects of their usage.

When federal wiretap regulation went into effect in 1968, the majority of wiretaps (64%) were used for investigating gambling cases. Since then, however, the share of wiretaps devoted to narcotics investigations has steadily increased. In 1994, narcotics investigations accounted for 77% of all cases using electronic surveillance. As the use of electronic surveillance increasingly became concentrated on narcotics investigations, the average wiretap became longer and more costly. Between 1968 and 1994, the average cost of a wiretap rose from 1,358 to 49,478 dollars, and the length doubled from 20 to nearly 40 days (p. 209). This shift was largely due to the lengthy timelines involved in drug investigations, which can span months or years.

Wiretaps occasionally result in large drug busts, which are often then used by advocates to defend the practice of wiretapping. It is unlikely that wiretaps actually affect the underlying problem of consumption, however, or that they are a better use of funds than alternative methods of stemming drug use (p. 211). The FBI has likewise cited the importance of wiretaps in investigations of kidnapping and domestic terrorism. Between 1968 and 1994, however, electronic surveillance only played a role in just 2-3 kidnapping cases per year, and domestic terrorism cases are more likely to be investigated under the Foreign Intelligence Surveillance Act (p. 211).

At the state level, 48 jurisdictions have laws that permit that authorize courts to issue orders for oral, wire, or electronic surveillance (AO, 2017). In 2017, the states with the most wiretaps were California, New York, Nevada, and North Carolina. Since the 1990s, California has seen a massive increase in wiretaps: from 8 in 1994 to 225 in 2017 (p. 212; AO, 2017).

Many of the trends first observed by Landau and Diffie have continued apace in the 21st century, but two major changes in wiretapping have occured since the 1990s. First, portable devices became the most commonly wiretapped devices as people switched from landlines to mobile phones. Because portable devices increased the number of daily communications per person, the first change begot a second: the government began intercepting ever-greater volumes of communications. The number of intercepted conversations has increased from around 400,000 in 1968 to over 2 million (pp. 214-215).

Landau and Diffie note several limitations in the Wiretap Report data. First, its statistics do not distinguish wiretaps from bugs. Second, due to reporting issues the data may actually underestimate the total number of intercepts installed. Third, the data lack important contextual information from the court hearings themselves.

Research Questions

Which types of wiretaps are the most expensive? Which are the longest? Which are most effective in terms of arrests and convictions?

Can we come up with a metric for assessing the efficiency of wiretaps? Maybe arrests/dollar spent, arrests/intercept? Can we rank jurisdictions based on these metrics?

Are there any notable differences between wiretaps that were reported vs. not reported?

Exploratory Data Analysis

In [1]:
#import libraries
%matplotlib inline

import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns

Federal Courts

In [2]:
#read federal intercept data
fed17 = pd.read_csv('fedwire_2017.csv')
fed17.head()
Out[2]:
Jurisdiction A.O. Number Judge Attorney General or Designee Offense Type Location Application Date Original Order (Days) Number of Extensions ... Total Cost($) Other Than Manpower Cost ($) Arrests Trials Motions Granted Motions Denied Motions Pending Persons Convicted Cost Related Results Related
0 ALABAMA, MIDDLE 2017-1 ALBRITTON, III NaN NARCOTICS WC D 02/28/2017 30.0 0.0 ... 269,859 0 3.0 0.0 0.0 0.0 0.0 0.0 NaN NaN
1 ALABAMA, MIDDLE 2017-2 ALBRITTON, III BLANCO DRUGS - ILLEGAL WC,TX D 04/05/2017 30.0 1.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 ALABAMA, MIDDLE 2017-3 ALBRITTON, III BLANCO DRUGS - ILLEGAL WC,TX D 02/28/2017 30.0 0.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 ALABAMA, MIDDLE 2017-4 ALBRITTON, III BLANCO DRUGS - ILLEGAL WC,TX D 03/07/2017 30.0 1.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 ALABAMA, MIDDLE 2017-5 ALBRITTON, III BLANCO DRUGS - ILLEGAL WC,TX D 04/05/2017 30.0 0.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 27 columns

In [3]:
fed17.Jurisdiction.unique()
Out[3]:
array(['ALABAMA, MIDDLE', 'ALABAMA, NORTHERN', 'ALABAMA, SOUTHERN',
       'ARIZONA', 'ARKANSAS, EASTERN', 'ARKANSAS, WESTERN',
       'CALIFORNIA, CENTRAL', 'CALIFORNIA, EASTERN',
       'CALIFORNIA, NORTHERN', 'CALIFORNIA, SOUTHERN', 'COLORADO',
       'CONNECTICUT', 'DELAWARE', 'DISTRICT OF COLUMBIA',
       'FLORIDA, MIDDLE', 'FLORIDA, NORTHERN', 'FLORIDA, SOUTHERN',
       'GEORGIA, MIDDLE', 'GEORGIA, NORTHERN', 'GEORGIA, SOUTHERN',
       'HAWAII', 'IDAHO', 'ILLINOIS, CENTRAL', 'ILLINOIS, NORTHERN',
       'ILLINOIS, SOUTHERN', 'INDIANA, NORTHERN', 'INDIANA, SOUTHERN',
       'IOWA, NORTHERN', 'IOWA, SOUTHERN', 'KANSAS', 'KENTUCKY, EASTERN',
       'KENTUCKY, WESTERN', 'LOUISIANA, EASTERN', 'LOUISIANA, MIDDLE',
       'LOUISIANA, WESTERN', 'MAINE', 'MARYLAND', 'MASSACHUSETTS',
       'MICHIGAN, EASTERN', 'MICHIGAN, WESTERN', 'MINNESOTA',
       'MISSISSIPPI, NORTHERN', 'MISSISSIPPI, SOUTHERN',
       'MISSOURI, EASTERN', 'MISSOURI, WESTERN', 'MONTANA', 'NEBRASKA',
       'NEVADA', 'NEW HAMPSHIRE', 'NEW JERSEY', 'NEW MEXICO',
       'NEW YORK, EASTERN', 'NEW YORK, NORTHERN', 'NEW YORK, SOUTHERN',
       'NEW YORK, WESTERN', 'NORTH CAROLINA, EASTERN',
       'NORTH CAROLINA, MIDDLE', 'NORTH CAROLINA, WESTERN',
       'OHIO, NORTHERN', 'OHIO, SOUTHERN', 'OKLAHOMA, NORTHERN',
       'OKLAHOMA, WESTERN', 'OREGON', 'PENNSYLVANIA, EASTERN',
       'PENNSYLVANIA, MIDDLE', 'PENNSYLVANIA, WESTERN', 'PUERTO RICO',
       'RHODE ISLAND', 'SOUTH CAROLINA', 'TENNESSEE, EASTERN',
       'TENNESSEE, MIDDLE', 'TENNESSEE, WESTERN', 'TEXAS, EASTERN',
       'TEXAS, NORTHERN', 'TEXAS, SOUTHERN', 'TEXAS, WESTERN', 'UTAH',
       'VIRGINIA, EASTERN', 'VIRGINIA, WESTERN', 'WASHINGTON, EASTERN',
       'WASHINGTON, WESTERN', 'WEST VIRGINIA, NORTHERN',
       'WEST VIRGINIA, SOUTHERN', 'WISCONSIN, EASTERN',
       'WISCONSIN, WESTERN', nan], dtype=object)
In [4]:
fed17.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3027 entries, 0 to 3026
Data columns (total 27 columns):
Jurisdiction                          3026 non-null object
A.O. Number                           3026 non-null object
Judge                                 3026 non-null object
Attorney General or Designee          992 non-null object
Offense                               3024 non-null object
Type                                  3026 non-null object
Location                              3026 non-null object
Application Date                      3026 non-null object
Original Order (Days)                 3019 non-null float64
Number of Extensions                  3026 non-null float64
Total Length (Days)                   3026 non-null float64
Installation                          3026 non-null object
Number of Days in Operation           1718 non-null float64
Avg. Intercepts Per Day               1688 non-null float64
Number of Persons Intercepts          1686 non-null object
Number of Intercepts                  1697 non-null object
Number of Incriminating Intercepts    1697 non-null object
Total Cost($)                         1578 non-null object
Other Than Manpower Cost ($)          1580 non-null object
Arrests                               1633 non-null float64
Trials                                1619 non-null float64
Motions Granted                       1621 non-null float64
Motions Denied                        1621 non-null float64
Motions Pending                       1621 non-null float64
Persons Convicted                     1619 non-null float64
Cost Related                          336 non-null object
Results Related                       514 non-null object
dtypes: float64(11), object(16)
memory usage: 638.6+ KB
In [5]:
#summarize data
fed17.describe()
Out[5]:
Original Order (Days) Number of Extensions Total Length (Days) Number of Days in Operation Avg. Intercepts Per Day Arrests Trials Motions Granted Motions Denied Motions Pending Persons Convicted
count 3019.000000 3026.000000 3026.000000 1718.000000 1688.000000 1633.000000 1619.000000 1621.000000 1621.000000 1621.000000 1619.000000
mean 29.836038 0.522802 45.279247 41.833527 414.114336 2.357012 0.017912 0.046885 0.024059 0.001234 0.362569
std 1.663931 1.097996 32.928960 33.149863 12546.685926 7.362417 0.318836 1.233393 0.641209 0.035115 2.941558
min 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
25% 30.000000 0.000000 30.000000 29.000000 2.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
50% 30.000000 0.000000 30.000000 30.000000 34.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
75% 30.000000 1.000000 60.000000 58.000000 115.250000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
max 30.000000 16.000000 510.000000 510.000000 515477.000000 130.000000 11.000000 40.000000 25.000000 1.000000 70.000000

Is the conviction rate worth the cost? We will see mean costs later.

Check for missing values

In [6]:
import missingno as msno
In [7]:
fed17.isnull().sum()
Out[7]:
Jurisdiction                             1
A.O. Number                              1
Judge                                    1
Attorney General or Designee          2035
Offense                                  3
Type                                     1
Location                                 1
Application Date                         1
Original Order (Days)                    8
Number of Extensions                     1
Total Length (Days)                      1
Installation                             1
Number of Days in Operation           1309
Avg. Intercepts Per Day               1339
Number of Persons Intercepts          1341
Number of Intercepts                  1330
Number of Incriminating Intercepts    1330
Total Cost($)                         1449
Other Than Manpower Cost ($)          1447
Arrests                               1394
Trials                                1408
Motions Granted                       1406
Motions Denied                        1406
Motions Pending                       1406
Persons Convicted                     1408
Cost Related                          2691
Results Related                       2513
dtype: int64
In [8]:
#Missing values matrix
msno.matrix(fed17.sample(100))
Out[8]:
<matplotlib.axes._subplots.AxesSubplot at 0x1171cbc88>

We only have consistent data for some of the variables. Many observations are missing values from Number of Days in Operation to Results Related. What do these observations have in common?

In [9]:
#How many wiretaps were reported by the prosecutor?
fed17['Installation'].value_counts()
Out[9]:
INSTALLED AND USED      1721
NO PROSECUTOR REPORT    1283
NEVER INSTALLED           22
Name: Installation, dtype: int64
In [10]:
# get rid of 'never installed'
fed17 = fed17[fed17.Installation != 'NEVER INSTALLED']
In [11]:
# very few records report AG or designee, so drop this column
fed17 = fed17.drop('Attorney General or Designee', axis=1)
In [12]:
# New dataframe: include only taps without prosecutor reports
fed_no = fed17.loc[fed17['Installation'] == 'NO PROSECUTOR REPORT']
In [13]:
# check missing values
msno.matrix(fed_no.sample(100))
Out[13]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a1e424860>

The missing values matrix above shows missing values for only the wiretaps that had no prosecutor report. The pattern of missing values in our full dataset stems from the fact that data for these variables were not submitted by the prosecutor, as the missing values makes clear.

Among the jurisdictions where prosecutor reports were not submitted, which rank the highest?

In [14]:
fed_no['Jurisdiction'].value_counts()
Out[14]:
TEXAS, SOUTHERN            86
CALIFORNIA, CENTRAL        85
ARIZONA                    82
COLORADO                   64
MISSOURI, EASTERN          51
NEW YORK, SOUTHERN         50
NEW JERSEY                 48
ILLINOIS, NORTHERN         40
CONNECTICUT                34
PENNSYLVANIA, WESTERN      30
LOUISIANA, EASTERN         29
CALIFORNIA, SOUTHERN       29
TEXAS, WESTERN             27
UTAH                       27
OKLAHOMA, WESTERN          27
DISTRICT OF COLUMBIA       21
FLORIDA, MIDDLE            20
MARYLAND                   20
KANSAS                     19
ARKANSAS, EASTERN          18
CALIFORNIA, NORTHERN       18
OHIO, SOUTHERN             17
SOUTH CAROLINA             16
INDIANA, SOUTHERN          16
NEW YORK, NORTHERN         15
PENNSYLVANIA, MIDDLE       15
FLORIDA, SOUTHERN          15
OREGON                     15
MISSOURI, WESTERN          14
MISSISSIPPI, SOUTHERN      14
                           ..
MICHIGAN, EASTERN           8
TENNESSEE, MIDDLE           7
NEVADA                      7
NORTH CAROLINA, WESTERN     7
MINNESOTA                   6
OKLAHOMA, NORTHERN          6
OHIO, NORTHERN              6
INDIANA, NORTHERN           6
ILLINOIS, SOUTHERN          6
WISCONSIN, EASTERN          5
ALABAMA, MIDDLE             5
LOUISIANA, WESTERN          5
MONTANA                     5
MASSACHUSETTS               4
KENTUCKY, EASTERN           4
WEST VIRGINIA, NORTHERN     4
HAWAII                      4
TEXAS, EASTERN              4
VIRGINIA, WESTERN           3
TEXAS, NORTHERN             3
ALABAMA, SOUTHERN           3
NEW HAMPSHIRE               3
MAINE                       2
MISSISSIPPI, NORTHERN       2
WISCONSIN, WESTERN          2
NORTH CAROLINA, EASTERN     1
GEORGIA, MIDDLE             1
FLORIDA, NORTHERN           1
VIRGINIA, EASTERN           1
WASHINGTON, EASTERN         1
Name: Jurisdiction, Length: 79, dtype: int64

For each jurisdiction, what percentage of total wiretaps were not accompanied by a prosecutor report?

In [15]:
percent = fed17.groupby(['Jurisdiction'])['Installation'].value_counts(normalize=True, ascending = False) * 100
In [16]:
percent = percent.unstack(level=1)
In [17]:
percent = percent.fillna(0)
percent = percent.round(1)
In [18]:
percent.sort_values(by='NO PROSECUTOR REPORT', ascending=False)
Out[18]:
Installation INSTALLED AND USED NO PROSECUTOR REPORT
Jurisdiction
WEST VIRGINIA, NORTHERN 0.0 100.0
PENNSYLVANIA, MIDDLE 11.8 88.2
ALABAMA, MIDDLE 16.7 83.3
MONTANA 16.7 83.3
NORTH CAROLINA, MIDDLE 20.0 80.0
MICHIGAN, WESTERN 23.1 76.9
NEW JERSEY 23.8 76.2
VIRGINIA, WESTERN 25.0 75.0
NEW YORK, WESTERN 25.0 75.0
ALABAMA, SOUTHERN 25.0 75.0
WEST VIRGINIA, SOUTHERN 28.6 71.4
COLORADO 31.9 68.1
PENNSYLVANIA, WESTERN 33.3 66.7
ILLINOIS, SOUTHERN 33.3 66.7
OKLAHOMA, NORTHERN 33.3 66.7
CONNECTICUT 35.8 64.2
TENNESSEE, MIDDLE 36.4 63.6
ARKANSAS, WESTERN 36.8 63.2
TENNESSEE, EASTERN 37.5 62.5
OREGON 37.5 62.5
MISSOURI, EASTERN 37.8 62.2
MISSISSIPPI, SOUTHERN 39.1 60.9
ARIZONA 39.3 60.7
ALABAMA, NORTHERN 40.0 60.0
UTAH 40.0 60.0
RHODE ISLAND 42.9 57.1
OKLAHOMA, WESTERN 43.8 56.2
KANSAS 44.1 55.9
LOUISIANA, EASTERN 44.2 55.8
MINNESOTA 45.5 54.5
... ... ...
MISSISSIPPI, NORTHERN 66.7 33.3
WISCONSIN, EASTERN 66.7 33.3
WASHINGTON, EASTERN 66.7 33.3
HAWAII 66.7 33.3
INDIANA, SOUTHERN 68.0 32.0
KENTUCKY, EASTERN 69.2 30.8
WASHINGTON, WESTERN 69.2 30.8
NEW MEXICO 69.2 30.8
ILLINOIS, NORTHERN 69.5 30.5
MARYLAND 69.7 30.3
TEXAS, WESTERN 70.3 29.7
GEORGIA, SOUTHERN 70.4 29.6
GEORGIA, NORTHERN 72.9 27.1
FLORIDA, SOUTHERN 74.6 25.4
LOUISIANA, WESTERN 78.3 21.7
NEW YORK, EASTERN 79.1 20.9
FLORIDA, NORTHERN 80.0 20.0
GEORGIA, MIDDLE 85.7 14.3
CALIFORNIA, SOUTHERN 86.4 13.6
MASSACHUSETTS 87.1 12.9
OHIO, NORTHERN 88.5 11.5
TEXAS, NORTHERN 90.6 9.4
NORTH CAROLINA, EASTERN 92.3 7.7
VIRGINIA, EASTERN 96.6 3.4
IDAHO 100.0 0.0
LOUISIANA, MIDDLE 100.0 0.0
IOWA, SOUTHERN 100.0 0.0
CALIFORNIA, EASTERN 100.0 0.0
DELAWARE 100.0 0.0
ILLINOIS, CENTRAL 100.0 0.0

85 rows × 2 columns

Now we know which jurisdictions are most likely not to submit prosecutor reports. Their low percentage of prosecutor reports might be due to a lack of personnel and resources, or perhaps because the prosecutors in those districts do not regard filing reports as a priority. The AO explains that reports may be missing because "some prosecutors may have delayed filing reports to avoid jeopardizing ongoing investigations. Some of the prosecutors’ reports require additional information to comply with reporting requirements or were received too late to include in this document. Information about these wiretaps should appear in future reports" (AO, 2017).

Regardless of why reports are missing, it should be noted that comparing percentages across jurisdictions may be misleading, because some jurisdictions have dozens of records while others have only several. Northern WVA is on the top of the list for percent of reports not filed, but it only ordered four wiretaps. New Jersey is near the top, but it had 63 wiretaps.

In order to evaluate wiretaps in terms of cost, timespan, arrests, and other factors, we will have to look only at records for which a prosecutor report was filed. Before doing so, however, we will look at the variables that are not dependent on prosecutor reporting to see what insights we might derive.

Jurisdiction
Judge
Location
Type
Offense
Application Date
Original Order (Days)
Number of Extensions
Total Length (Days)

In [19]:
# Number of wiretaps per jurisdiction
fed17['Jurisdiction'].value_counts()
Out[19]:
CALIFORNIA, SOUTHERN       213
TEXAS, SOUTHERN            189
CALIFORNIA, CENTRAL        175
ARIZONA                    135
ILLINOIS, NORTHERN         131
NEW YORK, SOUTHERN         107
COLORADO                    94
TEXAS, WESTERN              91
MISSOURI, EASTERN           82
MARYLAND                    66
NEW JERSEY                  63
FLORIDA, SOUTHERN           59
CONNECTICUT                 53
LOUISIANA, EASTERN          52
OHIO, NORTHERN              52
INDIANA, SOUTHERN           50
GEORGIA, NORTHERN           48
OKLAHOMA, WESTERN           48
FLORIDA, MIDDLE             46
UTAH                        45
PENNSYLVANIA, WESTERN       45
DISTRICT OF COLUMBIA        44
NEW YORK, EASTERN           43
SOUTH CAROLINA              40
NEW MEXICO                  39
NEW YORK, NORTHERN          38
ARKANSAS, EASTERN           37
CALIFORNIA, NORTHERN        37
KANSAS                      34
OHIO, SOUTHERN              34
                          ... 
ALABAMA, NORTHERN           15
RHODE ISLAND                14
WEST VIRGINIA, SOUTHERN     14
MICHIGAN, WESTERN           13
KENTUCKY, EASTERN           13
NORTH CAROLINA, EASTERN     13
NEW YORK, WESTERN           12
HAWAII                      12
LOUISIANA, MIDDLE           11
MINNESOTA                   11
TENNESSEE, MIDDLE           11
TEXAS, EASTERN              11
ILLINOIS, SOUTHERN           9
OKLAHOMA, NORTHERN           9
NEW HAMPSHIRE                8
GEORGIA, MIDDLE              7
MISSISSIPPI, NORTHERN        6
MONTANA                      6
ALABAMA, MIDDLE              6
FLORIDA, NORTHERN            5
DELAWARE                     5
WEST VIRGINIA, NORTHERN      4
MAINE                        4
WISCONSIN, WESTERN           4
ALABAMA, SOUTHERN            4
VIRGINIA, WESTERN            4
WASHINGTON, EASTERN          3
ILLINOIS, CENTRAL            2
IDAHO                        1
IOWA, SOUTHERN               1
Name: Jurisdiction, Length: 85, dtype: int64
In [20]:
#Number of wiretaps per offense
fed17['Offense'].value_counts()
Out[20]:
NARCOTICS               1822
DRUGS - ILLEGAL          525
CONSPIRACY               422
RACKETEERING              33
$LAUNDERING               32
OTHER                     28
CORRUPTION                21
FRAUD                     20
MURDER                    18
DRUGS - PRESCRIPTION      15
FIREARMS                  13
BRIBERY                   11
POSSESSION                 8
GAMBLING                   6
KIDNAPPING                 5
EXTORTION                  5
ROBBERY                    3
TERRORISM                  2
IMMIGRATION                2
LOANSHARKING               2
SMUGGLING                  2
USURY                      1
CIVIL RIGHTS               1
COERCION                   1
ASSAULT                    1
BOMBING                    1
THEFT                      1
EMBEZZLEMENT               1
Name: Offense, dtype: int64
In [21]:
#Number of wiretaps by type
fed17['Type'].value_counts()
Out[21]:
WC,TX          1247
WC             1152
TX              220
WC,TX,EO         59
WC,EO            59
EO               40
TX,EO            37
WC,WS            30
AP               28
WC,WO,TX         23
WC,OM,TX         19
WC,EE,TX,AP      17
OM                9
WC,TX,AP          8
WS                8
WC,OM             6
WC,OO,TX          5
WO,TX             4
OO                4
EE                4
WC,AP             3
-99               3
WC,WS,TX          3
TX,AP             2
OM,OO             2
OM,EO             2
WC,EE             2
WO                2
WC,AP,EO          2
WS,TX             1
WC,WO,EO          1
WC,OO             1
WC,WO             1
Name: Type, dtype: int64

Data Definitions for Wiretap Type:
WC = Cellular or Mobile Telephone (Wire)
WS = Standard Telephone (Wire)
WO = Other (Wire)
OM = Microphone (Oral)
OO = Other (Oral)
AP = App (Electronic)
ED = Digital Pager (Electronic)
EE = Computer or E-Mail (Electronic)
EF = Fax Machine (Electronic)
EO = Other (Electronic)
TX = Text Message (Electronic)

Note: Unlike previous data analyzed by Landau and Duffy, it appears that the AO now distinguishes between wiretaps and bugs.

Almost all wiretap orders are for cell phones and text messages.

Does the number of wiretap orders vary by month?

In [22]:
fed17['Application Date'].value_counts()
Out[22]:
03/27/2017    28
02/06/2017    21
11/03/2016    18
04/04/2017    18
09/23/2016    18
03/17/2017    17
07/07/2017    17
05/05/2017    16
03/06/2017    16
03/31/2017    15
11/04/2016    15
08/25/2016    15
08/23/2017    14
03/03/2017    14
04/05/2017    14
02/24/2017    14
01/18/2017    14
06/19/2017    14
01/13/2017    14
05/12/2017    14
04/27/2017    14
07/06/2017    14
04/06/2017    13
03/28/2017    13
02/17/2017    13
05/17/2017    13
06/12/2017    13
04/20/2017    13
01/27/2017    13
02/22/2017    13
              ..
10/11/2016     1
08/15/2015     1
10/16/2013     1
04/24/2012     1
09/09/2013     1
09/19/2014     1
07/31/2015     1
02/24/2012     1
02/04/2013     1
07/23/2017     1
03/25/2016     1
03/10/2015     1
01/30/2015     1
03/08/2013     1
05/30/2014     1
09/18/2015     1
12/18/2015     1
06/01/2012     1
05/07/2017     1
04/22/2011     1
12/30/2013     1
03/07/2013     1
09/19/2012     1
02/23/2015     1
10/11/2013     1
05/03/2016     1
02/03/2015     1
04/13/2016     1
11/25/2015     1
04/18/2013     1
Name: Application Date, Length: 700, dtype: int64
In [23]:
# want to get counts per month for just 2017
# first need to convert column to datetime format
fed17['Application Date'] = pd.to_datetime(fed17['Application Date'])
In [24]:
# extract year and month
fed17['year'] = pd.DatetimeIndex(fed17['Application Date']).year
fed17['month'] = pd.DatetimeIndex(fed17['Application Date']).month
In [25]:
plot = pd.DataFrame(fed17.loc[fed17['year'] == 2017].month.value_counts())
plot.reset_index(level=0, inplace=True)
plot = plot.sort_values(by='index', ascending=True)
In [26]:
plot = plot.rename(columns={"index": "month", "month": "count"})
plot.head()
Out[26]:
month count
5 1.0 162
3 2.0 191
0 3.0 232
2 4.0 194
1 5.0 199
In [27]:
sns.lineplot(x='month', y = 'count', data=plot)
Out[27]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a1a052748>

Wiretap orders peaked in March 2017 and then steadily declined through December.

In [28]:
#New dataframe: keep only records that were reported
fed_rep = fed17.loc[fed17['Installation'] == 'INSTALLED AND USED']
In [29]:
fed_rep.describe()
Out[29]:
Original Order (Days) Number of Extensions Total Length (Days) Number of Days in Operation Avg. Intercepts Per Day Arrests Trials Motions Granted Motions Denied Motions Pending Persons Convicted year month
count 1721.000000 1721.000000 1721.000000 1714.000000 1684.000000 1620.000000 1606.000000 1608.000000 1608.000000 1608.000000 1606.000000 1721.000000 1721.000000
mean 29.987798 0.534573 46.024985 41.931155 415.097981 2.375926 0.018057 0.047264 0.024254 0.001244 0.365504 2016.051714 6.335851
std 0.506208 1.070805 32.134238 33.126779 12561.570722 7.388875 0.320121 1.238364 0.643794 0.035256 2.953265 1.154465 3.305376
min 9.000000 0.000000 9.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 2010.000000 1.000000
25% 30.000000 0.000000 30.000000 29.000000 2.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 2016.000000 3.000000
50% 30.000000 0.000000 30.000000 30.000000 34.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 2016.000000 6.000000
75% 30.000000 1.000000 60.000000 58.000000 116.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 2017.000000 9.000000
max 30.000000 16.000000 510.000000 510.000000 515477.000000 130.000000 11.000000 40.000000 25.000000 1.000000 70.000000 2017.000000 12.000000
In [30]:
msno.matrix(fed_rep.sample(100))
Out[30]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a19850908>

Data still patchy in some places, but for the most part we have complete records

In [116]:
#Which criminal investigations involve the longest wiretaps?
fed_rep.groupby('Offense')['Total Length (Days)'].mean()
Out[116]:
Offense
$LAUNDERING         48.461538
BRIBERY             66.666667
CIVIL RIGHTS        30.000000
COERCION            90.000000
CONSPIRACY          46.480519
CORRUPTION         132.000000
DRUGS - ILLEGAL     73.333333
EMBEZZLEMENT        30.000000
EXTORTION          120.000000
FIREARMS            37.500000
FRAUD               62.142857
GAMBLING           108.000000
IMMIGRATION         30.000000
KIDNAPPING          30.000000
LOANSHARKING        75.000000
MURDER              76.153846
NARCOTICS           44.137475
OTHER               51.000000
POSSESSION          52.500000
RACKETEERING        47.500000
ROBBERY             30.000000
SMUGGLING           30.000000
THEFT               60.000000
USURY               60.000000
Name: Total Length (Days), dtype: float64

2017 data doesn't fit earlier assertion that wiretaps for narcotics are longer--look at gambling and corruption

In [32]:
fed_rep['Total Cost($)'] = fed_rep['Total Cost($)'].str.replace(',','')
/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
In [33]:
fed_rep.to_csv('fed_rep.csv')

Maps

In [34]:
#load mapping libraries
import geopandas as gpd
import pysal as ps
from pysal.contrib.viz import mapping as maps
import palettable as pltt
from seaborn import palplot
In [35]:
#create link to file directory
jur_dir = 'US_District_Court_Jurisdictions/'
#call file
court_jur = jur_dir + 'US_District_Court_Jurisdictions.shp'
#read shapefile with geopandas
jur = gpd.read_file(court_jur)
jur.set_index('DISTRICT')
Out[35]:
FID ABBR SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
DISTRICT
ALABAMA MIDDLE 1 ALM 1.557104e+04 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((-85.3434143069999 33.4967117310001, ...
ALABAMA NORTHERN 2 ALN 2.279241e+04 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((-88.1822509769999 35.0087089540001, ...
ALABAMA SOUTHERN 3 ALS 3.672870e+04 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((-88.0563507079999 30.1896820070001,...
ALASKA 4 AK 2.130451e+07 787.366223 279.696379 9 8.146772e+12 1.146259e+08 (POLYGON ((-179.098083496 51.3043937680002, -1...
ARIZONA 5 AZ 1.140598e+05 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-110.539299011 37.004226685, -110.47...
ARKANSAS EASTERN 6 ARE 2.887509e+04 20.282134 7.391100 8 1.119305e+11 2.444057e+06 POLYGON ((-90.6844940189999 36.4965057370001, ...
ARKANSAS WESTERN 7 ARW 2.429224e+04 17.572698 6.190137 8 9.333129e+10 2.130771e+06 POLYGON ((-93.3690338129999 36.496860504, -93....
CALIFORNIA CENTRAL 8 CAC 7.773755e+04 26.141797 10.235889 9 1.540900e+11 3.109362e+06 (POLYGON ((-118.571754456 33.0296134950001, -1...
CALIFORNIA EASTERN 9 CAE 9.178595e+04 32.204502 23.632805 9 3.745997e+11 4.011210e+06 (POLYGON ((-121.839935303 38.0620040890002, -1...
CALIFORNIA NORTHERN 10 CAN 2.673482e+04 29.120485 5.579357 9 8.854351e+10 3.691538e+06 (POLYGON ((-122.993621826 37.7011833190001, -1...
CALIFORNIA SOUTHERN 11 CAS 1.302978e+04 8.844054 2.191518 9 3.239572e+10 1.029109e+06 (POLYGON ((-117.215171814 32.7773666380001, -1...
COLORADO 12 CO 1.040584e+05 22.031328 28.054360 10 4.475472e+11 2.708929e+06 POLYGON ((-104.139907837 41.0019035340002, -10...
CONNECTICUT 13 CT 5.657781e+03 7.065003 1.393128 2 2.309454e+10 9.026114e+05 (POLYGON ((-71.96426391599989 41.3409652710002...
DELAWARE 14 DE 2.048253e+03 5.457967 0.552171 4 8.806052e+09 7.055051e+05 POLYGON ((-75.4797668459999 39.723167419, -75....
DISTRICT OF COLUMBIA 15 DC 6.015800e+01 0.716060 0.016194 District of Columbia 2.579056e+08 8.913811e+04 POLYGON ((-76.9320297239999 38.9103088380001, ...
FLORIDA MIDDLE 16 FLM 1.658333e+05 52.002268 6.976722 11 9.832156e+10 6.224150e+06 (POLYGON ((-81.4539489749999 25.8037090300001,...
FLORIDA NORTHERN 17 FLN 8.085151e+04 31.026591 4.136558 11 5.935770e+10 3.632281e+06 (POLYGON ((-82.76047515899999 28.9952297210001...
FLORIDA SOUTHERN 18 FLS 1.643553e+05 26.857592 2.399556 11 3.323875e+10 3.173552e+06 (POLYGON ((-81.95506286599991 24.5196857450001...
GEORGIA MIDDLE 19 GAM 2.514655e+04 17.223128 6.224920 11 9.116960e+10 2.067174e+06 POLYGON ((-83.09916686999991 34.5336227420001,...
GEORGIA NORTHERN 20 GAN 1.448429e+04 10.058370 3.664506 11 5.484908e+10 1.214110e+06 POLYGON ((-83.1025543209998 34.9880027770001, ...
GEORGIA SOUTHERN 21 GAS 3.158373e+04 22.657038 4.745704 11 6.947023e+10 2.736127e+06 (POLYGON ((-81.47998809799989 30.7580356600001...
HAWAII 22 HI 1.251206e+04 14.655890 1.449421 9 1.914824e+10 1.682499e+06 (POLYGON ((-155.849075317 20.2752361300001, -1...
IDAHO 23 ID 8.339463e+04 29.686713 24.456865 9 4.246831e+11 4.001904e+06 POLYGON ((-116.421066284 49.0015220640001, -11...
ILLINOIS CENTRAL 24 ILC 2.873903e+04 14.305483 7.894371 7 1.282952e+11 1.808202e+06 POLYGON ((-90.24733734099991 41.7720451350001,...
ILLINOIS SOUTHERN 25 ILS 1.665294e+04 14.690890 4.450294 7 7.036096e+10 1.820592e+06 POLYGON ((-87.6849517819999 39.4885253910002, ...
INDIANA NORTHERN 26 INN 1.344806e+04 9.512025 3.735276 7 6.137803e+10 1.205500e+06 POLYGON ((-85.6605987549998 41.7608070370002, ...
INDIANA SOUTHERN 27 INS 2.271327e+04 14.576477 6.141331 7 9.824338e+10 1.841569e+06 POLYGON ((-85.8640518189999 40.5666351320002, ...
IOWA NORTHERN 28 IAN 3.097177e+04 18.512013 8.825879 8 1.487987e+11 2.246330e+06 POLYGON ((-93.4964294429999 43.5039520260001, ...
IOWA SOUTHERN 29 IAS 2.525273e+04 17.607201 7.042830 8 1.162024e+11 2.139765e+06 POLYGON ((-92.7687377929999 42.2101135250002, ...
KANSAS 30 KS 8.222810e+04 21.349084 22.007447 10 3.484898e+11 2.561317e+06 POLYGON ((-100.194602966 40.0027618410001, -10...
... ... ... ... ... ... ... ... ... ...
OREGON 65 OR 1.112276e+05 32.263079 28.140968 9 4.845273e+11 4.112844e+06 (POLYGON ((-123.951034546 45.5330009460001, -1...
PENNSYLVANIA EASTERN 66 PAE 4.766876e+03 6.170629 1.308281 3 2.124152e+10 7.492982e+05 POLYGON ((-75.06324768099999 40.8521308900002,...
PENNSYLVANIA MIDDLE 67 PAM 2.129589e+04 14.248610 5.911726 3 9.709238e+10 1.725258e+06 POLYGON ((-76.5228729249999 42.0004882810001, ...
PENNSYLVANIA WESTERN 68 PAW 1.916885e+04 11.336522 5.311254 3 8.706643e+10 1.440796e+06 POLYGON ((-79.76249694799991 42.2678756710001,...
PUERTO RICO 69 PR 4.235475e+03 7.711394 0.764548 1 9.974549e+09 8.740196e+05 (POLYGON ((-66.5204467769999 17.8896942140002,...
RHODE ISLAND 70 RI 1.896307e+03 5.895388 0.306881 1 5.091440e+09 7.839080e+05 (POLYGON ((-71.56297302199989 41.182472229, -7...
SOUTH CAROLINA 71 SC 5.060914e+04 24.276642 7.802899 4 1.165366e+11 2.919215e+06 (POLYGON ((-80.7357711789999 32.3037033080001,...
SOUTH DAKOTA 72 SD 7.696602e+04 23.271515 22.580292 8 3.920090e+11 2.890312e+06 POLYGON ((-102.444473267 45.9440002440002, -10...
TENNESSEE EASTERN 73 TNE 1.653215e+04 14.502888 4.270485 6 6.525971e+10 1.713725e+06 POLYGON ((-81.6474533079999 36.6124992370001, ...
TENNESSEE MIDDLE 74 TNM 1.429762e+04 11.867564 3.702203 6 5.671259e+10 1.417736e+06 POLYGON ((-87.8504638669998 36.665153503, -87....
TENNESSEE WESTERN 75 TNW 1.179143e+04 9.980872 2.919934 6 4.455694e+10 1.190626e+06 POLYGON ((-89.1012649539998 36.505458832, -88....
TEXAS EASTERN 76 TXE 3.377511e+04 23.679032 8.354699 5 1.222746e+11 2.877492e+06 POLYGON ((-95.1663208009999 33.9377098080001, ...
TEXAS NORTHERN 77 TXN 9.678938e+04 32.874351 24.300239 5 3.610171e+11 4.000867e+06 POLYGON ((-101.625549316 36.5024604800001, -10...
TEXAS SOUTHERN 78 TXS 1.031682e+05 44.860009 10.328502 5 1.456691e+11 5.294942e+06 (POLYGON ((-97.2174911499999 26.0096778870001,...
TEXAS WESTERN 79 TXW 9.147135e+04 41.498785 22.251636 5 3.202545e+11 4.905598e+06 POLYGON ((-101.691505432 32.5252189640001, -10...
UTAH 80 UT 8.480087e+04 19.998615 22.969884 10 3.682100e+11 2.556782e+06 POLYGON ((-111.519134521 42.002731323, -111.51...
VERMONT 81 VT 9.613722e+03 9.128875 2.803203 2 4.835953e+10 1.196964e+06 POLYGON ((-73.0538635249999 45.0163345340001, ...
VIRGIN ISLANDS 82 VI 7.508860e+02 2.656416 0.029757 3 3.876620e+08 3.009570e+05 (POLYGON ((-64.7345428469999 17.765647888, -64...
VIRGINIA EASTERN 83 VAE 3.418347e+04 45.533646 4.047368 4 6.328401e+10 5.617558e+06 (POLYGON ((-75.9206466669999 36.5646018980001,...
VIRGINIA WESTERN 84 VAW 2.462238e+04 19.886863 6.503469 4 1.015817e+11 2.378741e+06 POLYGON ((-77.85482788099991 39.1091995240002,...
WASHINGTON EASTERN 85 WAE 4.233955e+04 18.033116 13.098106 9 2.397890e+11 2.338462e+06 POLYGON ((-120.85710144 49.0009841920001, -119...
WASHINGTON WESTERN 86 WAW 7.783449e+04 51.785243 7.727398 9 1.415048e+11 6.949933e+06 (POLYGON ((-122.324768066 45.5604209900001, -1...
WEST VIRGINIA NORTHERN 87 WVN 1.272775e+04 14.858897 3.435725 4 5.486757e+10 1.883769e+06 POLYGON ((-80.5190734859999 40.6385536190002, ...
WEST VIRGINIA SOUTHERN 88 WVS 1.145863e+04 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((-81.36780548099991 39.330371857, -81...
WISCONSIN EASTERN 89 WIE 2.456767e+04 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((-87.4881210329999 45.055072784, -87...
WISCONSIN WESTERN 90 WIW 6.301444e+04 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((-90.7892837519999 46.7264366150002,...
WYOMING 91 WY 9.762655e+04 21.992861 27.967965 10 4.741542e+11 2.776293e+06 POLYGON ((-109.172279358 45.0041122440001, -10...
ILLINOIS NORTHERN 92 ILN 0.000000e+00 9.641309 3.073970 7 5.117535e+10 1.183730e+06 POLYGON ((-87.5240020749999 41.7134971620002, ...
GUAM 93 GU 0.000000e+00 1.751627 0.046760 9 5.957803e+08 1.976879e+05 (POLYGON ((144.675288000412 13.2399350002825, ...
NORTHERN MARIANA ISLANDS 94 MP 0.000000e+00 3.651838 0.038870 9 5.010170e+08 4.154918e+05 (POLYGON ((144.898767929638 20.5282141269284, ...

94 rows × 9 columns

In [36]:
jur.plot()
Out[36]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a24db86a0>
In [37]:
#remove jurisdictions that cause scaling problems when plotting
jur = jur[jur.DISTRICT != 'GUAM']
jur = jur[jur.DISTRICT != 'ALASKA']
jur = jur[jur.DISTRICT != 'NORTHERN MARIANA ISLANDS']
jur = jur[jur.DISTRICT != 'VIRGIN ISLANDS']
jur = jur[jur.DISTRICT != 'HAWAII']
jur = jur[jur.DISTRICT != 'PUERTO RICO']
jur = jur[jur.DISTRICT != 'BERMUDA']
In [38]:
jur.plot()
Out[38]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a260265f8>
In [39]:
#check dataframe and projection
jur.crs
Out[39]:
{'init': 'epsg:4326'}
In [40]:
#UTM Zone 14
jur.to_crs("+proj=utm +zone=14 +ellps=WGS84 +datum=WGS84 +units=m +no_defs").plot()
Out[40]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a26177ac8>
In [41]:
#Massachusetts mainland
jur.to_crs({'init': 'epsg:2805'}).plot()
Out[41]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a260a7780>
In [42]:
#World Mercator projection
jur.to_crs({'init': 'epsg:3395'}).plot()
Out[42]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a26142a90>
In [43]:
jur = jur.to_crs("+proj=utm +zone=14 +ellps=WGS84 +datum=WGS84 +units=m +no_defs")
In [44]:
#Set up figure and axis with different size
f, ax = plt.subplots(1, figsize=(15,15))
#Add layer of polygons on the axis
jur.plot(ax=ax)
#Remove axis
ax.set_axis_off()
#Display
plt.show()
In [45]:
fed17.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 3005 entries, 0 to 3026
Data columns (total 28 columns):
Jurisdiction                          3004 non-null object
A.O. Number                           3004 non-null object
Judge                                 3004 non-null object
Offense                               3002 non-null object
Type                                  3004 non-null object
Location                              3004 non-null object
Application Date                      3004 non-null datetime64[ns]
Original Order (Days)                 2997 non-null float64
Number of Extensions                  3004 non-null float64
Total Length (Days)                   3004 non-null float64
Installation                          3004 non-null object
Number of Days in Operation           1714 non-null float64
Avg. Intercepts Per Day               1684 non-null float64
Number of Persons Intercepts          1673 non-null object
Number of Intercepts                  1684 non-null object
Number of Incriminating Intercepts    1684 non-null object
Total Cost($)                         1574 non-null object
Other Than Manpower Cost ($)          1574 non-null object
Arrests                               1620 non-null float64
Trials                                1606 non-null float64
Motions Granted                       1608 non-null float64
Motions Denied                        1608 non-null float64
Motions Pending                       1608 non-null float64
Persons Convicted                     1606 non-null float64
Cost Related                          335 non-null object
Results Related                       510 non-null object
year                                  3004 non-null float64
month                                 3004 non-null float64
dtypes: datetime64[ns](1), float64(13), object(14)
memory usage: 680.8+ KB
In [46]:
jur.reset_index()
Out[46]:
index FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
0 0 1 ALM ALABAMA MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
1 1 2 ALN ALABAMA NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
2 2 3 ALS ALABAMA SOUTHERN 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
3 4 5 AZ ARIZONA 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
4 5 6 ARE ARKANSAS EASTERN 28875.088 20.282134 7.391100 8 1.119305e+11 2.444057e+06 POLYGON ((1245497.465320354 4071327.816416644,...
5 6 7 ARW ARKANSAS WESTERN 24292.235 17.572698 6.190137 8 9.333129e+10 2.130771e+06 POLYGON ((1004541.982884312 4053833.637892126,...
6 7 8 CAC CALIFORNIA CENTRAL 77737.552 26.141797 10.235889 9 1.540900e+11 3.109362e+06 (POLYGON ((-1341965.065576056 3830213.72658085...
7 8 9 CAE CALIFORNIA EASTERN 91785.949 32.204502 23.632805 9 3.745997e+11 4.011210e+06 (POLYGON ((-1515460.750063287 4468009.65215679...
8 9 10 CAN CALIFORNIA NORTHERN 26734.821 29.120485 5.579357 9 8.854351e+10 3.691538e+06 (POLYGON ((-1629578.215244076 4454734.98264079...
9 10 11 CAS CALIFORNIA SOUTHERN 13029.776 8.844054 2.191518 9 3.239572e+10 1.029109e+06 (POLYGON ((-1217650.238149849 3777528.40702273...
10 11 12 CO COLORADO 104058.357 22.031328 28.054360 10 4.475472e+11 2.708929e+06 POLYGON ((67657.0177356624 4551709.815303136, ...
11 12 13 CT CONNECTICUT 5657.781 7.065003 1.393128 2 2.309454e+10 9.026114e+05 (POLYGON ((2770162.898902574 4945058.499139016...
12 13 14 DE DELAWARE 2048.253 5.457967 0.552171 4 8.806052e+09 7.055051e+05 POLYGON ((2525012.190007585 4671140.936623267,...
13 14 15 DC DISTRICT OF COLUMBIA 60.158 0.716060 0.016194 District of Columbia 2.579056e+08 8.913811e+04 POLYGON ((2422537.36391487 4545973.8173753, 24...
14 15 16 FLM FLORIDA MIDDLE 165833.265 52.002268 6.976722 11 9.832156e+10 6.224150e+06 (POLYGON ((2276135.892697865 2974845.523571447...
15 16 17 FLN FLORIDA NORTHERN 80851.513 31.026591 4.136558 11 5.935770e+10 3.632281e+06 (POLYGON ((2093082.81187928 3318807.819295, 20...
16 17 18 FLS FLORIDA SOUTHERN 164355.334 26.857592 2.399556 11 3.323875e+10 3.173552e+06 (POLYGON ((2243535.128965369 2821581.791515818...
17 18 19 GAM GEORGIA MIDDLE 25146.547 17.223128 6.224920 11 9.116960e+10 2.067174e+06 POLYGON ((1965794.615244794 3938419.85201234, ...
18 19 20 GAN GEORGIA NORTHERN 14484.294 10.058370 3.664506 11 5.484908e+10 1.214110e+06 POLYGON ((1957187.794888234 3989419.325908749,...
19 20 21 GAS GEORGIA SOUTHERN 31583.732 22.657038 4.745704 11 6.947023e+10 2.736127e+06 (POLYGON ((2189237.169172099 3537505.745217577...
20 22 23 ID IDAHO 83394.626 29.686713 24.456865 9 4.246831e+11 4.001904e+06 POLYGON ((-771178.8379696188 5575608.381396202...
21 23 24 ILC ILLINOIS CENTRAL 28739.027 14.305483 7.894371 7 1.282952e+11 1.808202e+06 POLYGON ((1227770.955034963 4661652.090511092,...
22 24 25 ILS ILLINOIS SOUTHERN 16652.942 14.690890 4.450294 7 7.036096e+10 1.820592e+06 POLYGON ((1474210.560531489 4432606.679928833,...
23 25 26 INN INDIANA NORTHERN 13448.055 9.512025 3.735276 7 6.137803e+10 1.205500e+06 POLYGON ((1609940.607110337 4710108.963379314,...
24 26 27 INS INDIANA SOUTHERN 22713.272 14.576477 6.141331 7 9.824338e+10 1.841569e+06 POLYGON ((1613413.384194463 4574452.409999062,...
25 27 28 IAN IOWA NORTHERN 30971.774 18.512013 8.825879 8 1.487987e+11 2.246330e+06 POLYGON ((944939.5053521493 4831514.495536662,...
26 28 29 IAS IOWA SOUTHERN 25252.726 17.607201 7.042830 8 1.162024e+11 2.139765e+06 POLYGON ((1014456.124073487 4691939.629893651,...
27 29 30 KS KANSAS 82228.102 21.349084 22.007447 10 3.484898e+11 2.561317e+06 POLYGON ((398031.8593221063 4428747.136296699,...
28 30 31 KYE KENTUCKY EASTERN 20552.690 13.002380 5.447175 6 8.541071e+10 1.590641e+06 POLYGON ((1746192.490879328 4424648.461906386,...
29 31 32 KYW KENTUCKY WESTERN 20012.843 15.789508 5.207829 6 8.109773e+10 1.914053e+06 (POLYGON ((1353697.286969651 4083820.057444283...
... ... ... ... ... ... ... ... ... ... ... ...
58 60 61 OHS OHIO SOUTHERN 23131.694 14.042997 6.295082 6 1.013534e+11 1.761304e+06 POLYGON ((1885220.966742389 4631763.113647775,...
59 61 62 OKE OKLAHOMA EASTERN 21529.654 14.006345 5.493813 10 8.293839e+10 1.739996e+06 POLYGON ((805192.0710524982 4004211.113418561,...
60 62 63 OKN OKLAHOMA NORTHERN 8907.036 8.140655 2.320193 10 3.575436e+10 9.838026e+05 POLYGON ((809362.1916551577 4100627.833549995,...
61 63 64 OKW OKLAHOMA WESTERN 39458.452 20.766551 10.191407 10 1.557325e+11 2.489025e+06 POLYGON ((499951.8033027247 4094957.005900142,...
62 64 65 OR OREGON 111227.563 32.263079 28.140968 9 4.845273e+11 4.112844e+06 (POLYGON ((-1445288.72228841 5354261.579318986...
63 65 66 PAE PENNSYLVANIA EASTERN 4766.876 6.170629 1.308281 3 2.124152e+10 7.492982e+05 POLYGON ((2524781.279156603 4807983.201911956,...
64 66 67 PAM PENNSYLVANIA MIDDLE 21295.892 14.248610 5.911726 3 9.709238e+10 1.725258e+06 POLYGON ((2365511.622367114 4901525.545709691,...
65 67 68 PAW PENNSYLVANIA WESTERN 19168.855 11.336522 5.311254 3 8.706643e+10 1.440796e+06 POLYGON ((2088917.995722735 4862560.459365817,...
66 69 70 RI RHODE ISLAND 1896.307 5.895388 0.306881 1 5.091440e+09 7.839080e+05 (POLYGON ((2810096.154486801 4938827.269533045...
67 70 71 SC SOUTH CAROLINA 50609.140 24.276642 7.802899 4 1.165366e+11 2.919215e+06 (POLYGON ((2231859.420064836 3724753.938132987...
68 71 72 SD SOUTH DAKOTA 76966.018 23.271515 22.580292 8 3.920090e+11 2.890312e+06 POLYGON ((233022.6608998735 5093596.248264234,...
69 72 73 TNE TENNESSEE EASTERN 16532.146 14.502888 4.270485 6 6.525971e+10 1.713725e+06 POLYGON ((2058451.086124036 4195167.543042691,...
70 73 74 TNM TENNESSEE MIDDLE 14297.618 11.867564 3.702203 6 5.671259e+10 1.417736e+06 POLYGON ((1498172.566152157 4116147.478395873,...
71 74 75 TNW TENNESSEE WESTERN 11791.426 9.980872 2.919934 6 4.455694e+10 1.190626e+06 POLYGON ((1387709.525154346 4085896.166051967,...
72 75 76 TXE TEXAS EASTERN 33775.112 23.679032 8.354699 5 1.222746e+11 2.877492e+06 POLYGON ((854390.4930180375 3761874.538346393,...
73 76 77 TXN TEXAS NORTHERN 96789.377 32.874351 24.300239 5 3.610171e+11 4.000867e+06 POLYGON ((264851.4101135639 4042887.266815202,...
74 77 78 TXS TEXAS SOUTHERN 103168.160 44.860009 10.328502 5 1.456691e+11 5.294942e+06 (POLYGON ((678392.5435317336 2877972.745050528...
75 78 79 TXW TEXAS WESTERN 91471.346 41.498785 22.251636 5 3.202545e+11 4.905598e+06 POLYGON ((247193.2798367047 3601848.732447769,...
76 79 80 UT UTAH 84800.868 19.998615 22.969884 10 3.682100e+11 2.556782e+06 POLYGON ((-537583.857536994 4726578.586626041,...
77 80 81 VT VERMONT 9613.722 9.128875 2.803203 2 4.835953e+10 1.196964e+06 POLYGON ((2542165.426297034 5323450.872719669,...
78 82 83 VAE VIRGINIA EASTERN 34183.467 45.533646 4.047368 4 6.328401e+10 5.617558e+06 (POLYGON ((2580567.214591374 4304281.838834392...
79 83 84 VAW VIRGINIA WESTERN 24622.384 19.886863 6.503469 4 1.015817e+11 2.378741e+06 POLYGON ((2336045.461008049 4548146.570400048,...
80 84 85 WAE WASHINGTON EASTERN 42339.553 18.033116 13.098106 9 2.397890e+11 2.338462e+06 POLYGON ((-1092380.909345797 5662104.909743664...
81 85 86 WAW WASHINGTON WESTERN 77834.495 51.785243 7.727398 9 1.415048e+11 6.949933e+06 (POLYGON ((-1318108.57937328 5316903.563073479...
82 86 87 WVN WEST VIRGINIA NORTHERN 12727.747 14.858897 3.435725 4 5.486757e+10 1.883769e+06 POLYGON ((2066524.575578697 4666331.385270311,...
83 87 88 WVS WEST VIRGINIA SOUTHERN 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
84 88 89 WIE WISCONSIN EASTERN 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
85 89 90 WIW WISCONSIN WESTERN 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...
86 90 91 WY WYOMING 97626.547 21.992861 27.967965 10 4.741542e+11 2.776293e+06 POLYGON ((-301660.0375465323 5033998.170901848...
87 91 92 ILN ILLINOIS NORTHERN 0.000 9.641309 3.073970 7 5.117535e+10 1.183730e+06 POLYGON ((1455374.792921589 4682088.144150389,...

88 rows × 11 columns

In [47]:
print(jur.DISTRICT.unique())
['ALABAMA MIDDLE' 'ALABAMA NORTHERN' 'ALABAMA SOUTHERN' 'ARIZONA'
 'ARKANSAS EASTERN' 'ARKANSAS WESTERN' 'CALIFORNIA CENTRAL'
 'CALIFORNIA EASTERN' 'CALIFORNIA NORTHERN' 'CALIFORNIA SOUTHERN'
 'COLORADO' 'CONNECTICUT' 'DELAWARE' 'DISTRICT OF COLUMBIA'
 'FLORIDA MIDDLE' 'FLORIDA NORTHERN' 'FLORIDA SOUTHERN' 'GEORGIA MIDDLE'
 'GEORGIA NORTHERN' 'GEORGIA SOUTHERN' 'IDAHO' 'ILLINOIS CENTRAL'
 'ILLINOIS SOUTHERN' 'INDIANA NORTHERN' 'INDIANA SOUTHERN' 'IOWA NORTHERN'
 'IOWA SOUTHERN' 'KANSAS' 'KENTUCKY EASTERN' 'KENTUCKY WESTERN'
 'LOUISIANA EASTERN' 'LOUISIANA MIDDLE' 'LOUISIANA WESTERN' 'MAINE'
 'MARYLAND' 'MASSACHUSETTS' 'MICHIGAN EASTERN' 'MICHIGAN WESTERN'
 'MINNESOTA' 'MISSISSIPPI NORTHERN' 'MISSISSIPPI SOUTHERN'
 'MISSOURI EASTERN' 'MISSOURI WESTERN' 'MONTANA' 'NEBRASKA' 'NEVADA'
 'NEW HAMPSHIRE' 'NEW JERSEY' 'NEW MEXICO' 'NEW YORK EASTERN'
 'NEW YORK NORTHERN' 'NEW YORK SOUTHERN' 'NEW YORK WESTERN'
 'NORTH CAROLINA EASTERN' 'NORTH CAROLINA MIDDLE' 'NORTH CAROLINA WESTERN'
 'NORTH DAKOTA' 'OHIO NORTHERN' 'OHIO SOUTHERN' 'OKLAHOMA EASTERN'
 'OKLAHOMA NORTHERN' 'OKLAHOMA WESTERN' 'OREGON' 'PENNSYLVANIA EASTERN'
 'PENNSYLVANIA MIDDLE' 'PENNSYLVANIA WESTERN' 'RHODE ISLAND'
 'SOUTH CAROLINA' 'SOUTH DAKOTA' 'TENNESSEE EASTERN' 'TENNESSEE MIDDLE'
 'TENNESSEE WESTERN' 'TEXAS EASTERN' 'TEXAS NORTHERN' 'TEXAS SOUTHERN'
 'TEXAS WESTERN' 'UTAH' 'VERMONT' 'VIRGINIA EASTERN' 'VIRGINIA WESTERN'
 'WASHINGTON EASTERN' 'WASHINGTON WESTERN' 'WEST VIRGINIA NORTHERN'
 'WEST VIRGINIA SOUTHERN' 'WISCONSIN EASTERN' 'WISCONSIN WESTERN'
 'WYOMING' 'ILLINOIS NORTHERN']
In [48]:
print(fed17.Jurisdiction.unique())
['ALABAMA, MIDDLE' 'ALABAMA, NORTHERN' 'ALABAMA, SOUTHERN' 'ARIZONA'
 'ARKANSAS, EASTERN' 'ARKANSAS, WESTERN' 'CALIFORNIA, CENTRAL'
 'CALIFORNIA, EASTERN' 'CALIFORNIA, NORTHERN' 'CALIFORNIA, SOUTHERN'
 'COLORADO' 'CONNECTICUT' 'DELAWARE' 'DISTRICT OF COLUMBIA'
 'FLORIDA, MIDDLE' 'FLORIDA, NORTHERN' 'FLORIDA, SOUTHERN'
 'GEORGIA, MIDDLE' 'GEORGIA, NORTHERN' 'GEORGIA, SOUTHERN' 'HAWAII'
 'IDAHO' 'ILLINOIS, CENTRAL' 'ILLINOIS, NORTHERN' 'ILLINOIS, SOUTHERN'
 'INDIANA, NORTHERN' 'INDIANA, SOUTHERN' 'IOWA, NORTHERN' 'IOWA, SOUTHERN'
 'KANSAS' 'KENTUCKY, EASTERN' 'KENTUCKY, WESTERN' 'LOUISIANA, EASTERN'
 'LOUISIANA, MIDDLE' 'LOUISIANA, WESTERN' 'MAINE' 'MARYLAND'
 'MASSACHUSETTS' 'MICHIGAN, EASTERN' 'MICHIGAN, WESTERN' 'MINNESOTA'
 'MISSISSIPPI, NORTHERN' 'MISSISSIPPI, SOUTHERN' 'MISSOURI, EASTERN'
 'MISSOURI, WESTERN' 'MONTANA' 'NEBRASKA' 'NEVADA' 'NEW HAMPSHIRE'
 'NEW JERSEY' 'NEW MEXICO' 'NEW YORK, EASTERN' 'NEW YORK, NORTHERN'
 'NEW YORK, SOUTHERN' 'NEW YORK, WESTERN' 'NORTH CAROLINA, EASTERN'
 'NORTH CAROLINA, MIDDLE' 'NORTH CAROLINA, WESTERN' 'OHIO, NORTHERN'
 'OHIO, SOUTHERN' 'OKLAHOMA, NORTHERN' 'OKLAHOMA, WESTERN' 'OREGON'
 'PENNSYLVANIA, EASTERN' 'PENNSYLVANIA, MIDDLE' 'PENNSYLVANIA, WESTERN'
 'PUERTO RICO' 'RHODE ISLAND' 'SOUTH CAROLINA' 'TENNESSEE, EASTERN'
 'TENNESSEE, MIDDLE' 'TENNESSEE, WESTERN' 'TEXAS, EASTERN'
 'TEXAS, NORTHERN' 'TEXAS, SOUTHERN' 'TEXAS, WESTERN' 'UTAH'
 'VIRGINIA, EASTERN' 'VIRGINIA, WESTERN' 'WASHINGTON, EASTERN'
 'WASHINGTON, WESTERN' 'WEST VIRGINIA, NORTHERN' 'WEST VIRGINIA, SOUTHERN'
 'WISCONSIN, EASTERN' 'WISCONSIN, WESTERN' nan]
In [49]:
#I want to merge shapefiles with Federal Wiretap reports on Jurisdiction/District. 
#The problem is that there are inconsistencies in the spellings.

#I will modify the shapefile rather than fed17, because it is likely that I will work with 
#wiretap files from other years that are formatted the same as fed17. Better to modify the 
#shapefile once so that its formatting is consistent with the other files in the future. 

#I need to replace each space in the shapefile with a comma. 
In [50]:
jur['DISTRICT'] = jur['DISTRICT'].str.replace(' ', ', ')
In [51]:
print(jur.DISTRICT.unique())
['ALABAMA, MIDDLE' 'ALABAMA, NORTHERN' 'ALABAMA, SOUTHERN' 'ARIZONA'
 'ARKANSAS, EASTERN' 'ARKANSAS, WESTERN' 'CALIFORNIA, CENTRAL'
 'CALIFORNIA, EASTERN' 'CALIFORNIA, NORTHERN' 'CALIFORNIA, SOUTHERN'
 'COLORADO' 'CONNECTICUT' 'DELAWARE' 'DISTRICT, OF, COLUMBIA'
 'FLORIDA, MIDDLE' 'FLORIDA, NORTHERN' 'FLORIDA, SOUTHERN'
 'GEORGIA, MIDDLE' 'GEORGIA, NORTHERN' 'GEORGIA, SOUTHERN' 'IDAHO'
 'ILLINOIS, CENTRAL' 'ILLINOIS, SOUTHERN' 'INDIANA, NORTHERN'
 'INDIANA, SOUTHERN' 'IOWA, NORTHERN' 'IOWA, SOUTHERN' 'KANSAS'
 'KENTUCKY, EASTERN' 'KENTUCKY, WESTERN' 'LOUISIANA, EASTERN'
 'LOUISIANA, MIDDLE' 'LOUISIANA, WESTERN' 'MAINE' 'MARYLAND'
 'MASSACHUSETTS' 'MICHIGAN, EASTERN' 'MICHIGAN, WESTERN' 'MINNESOTA'
 'MISSISSIPPI, NORTHERN' 'MISSISSIPPI, SOUTHERN' 'MISSOURI, EASTERN'
 'MISSOURI, WESTERN' 'MONTANA' 'NEBRASKA' 'NEVADA' 'NEW, HAMPSHIRE'
 'NEW, JERSEY' 'NEW, MEXICO' 'NEW, YORK, EASTERN' 'NEW, YORK, NORTHERN'
 'NEW, YORK, SOUTHERN' 'NEW, YORK, WESTERN' 'NORTH, CAROLINA, EASTERN'
 'NORTH, CAROLINA, MIDDLE' 'NORTH, CAROLINA, WESTERN' 'NORTH, DAKOTA'
 'OHIO, NORTHERN' 'OHIO, SOUTHERN' 'OKLAHOMA, EASTERN'
 'OKLAHOMA, NORTHERN' 'OKLAHOMA, WESTERN' 'OREGON' 'PENNSYLVANIA, EASTERN'
 'PENNSYLVANIA, MIDDLE' 'PENNSYLVANIA, WESTERN' 'RHODE, ISLAND'
 'SOUTH, CAROLINA' 'SOUTH, DAKOTA' 'TENNESSEE, EASTERN'
 'TENNESSEE, MIDDLE' 'TENNESSEE, WESTERN' 'TEXAS, EASTERN'
 'TEXAS, NORTHERN' 'TEXAS, SOUTHERN' 'TEXAS, WESTERN' 'UTAH' 'VERMONT'
 'VIRGINIA, EASTERN' 'VIRGINIA, WESTERN' 'WASHINGTON, EASTERN'
 'WASHINGTON, WESTERN' 'WEST, VIRGINIA, NORTHERN'
 'WEST, VIRGINIA, SOUTHERN' 'WISCONSIN, EASTERN' 'WISCONSIN, WESTERN'
 'WYOMING' 'ILLINOIS, NORTHERN']
In [52]:
#The above command caused problems for two-word state names

#New York
jur['DISTRICT'] = jur['DISTRICT'].str.replace('W, Y', 'W Y')
#New Hampshire
jur['DISTRICT'] = jur['DISTRICT'].str.replace('W, H', 'W H')
#New Jersey
jur['DISTRICT'] = jur['DISTRICT'].str.replace('W, J', 'W J')
#West Virginia
jur['DISTRICT'] = jur['DISTRICT'].str.replace('T, V', 'T V')
#North Carolina, South Carolina
jur['DISTRICT'] = jur['DISTRICT'].str.replace('H, C', 'H C')
#New Mexico
jur['DISTRICT'] = jur['DISTRICT'].str.replace('W, M', 'W M')
#North Dakota, South Dakota
jur['DISTRICT'] = jur['DISTRICT'].str.replace('H, D', 'H D')
#District of Columbia
jur['DISTRICT'] = jur['DISTRICT'].str.replace('T, OF, C', 'T OF C')
#Rhode Island
jur['DISTRICT'] = jur['DISTRICT'].str.replace('E, I', 'E I')
In [53]:
print(jur.DISTRICT.unique())
['ALABAMA, MIDDLE' 'ALABAMA, NORTHERN' 'ALABAMA, SOUTHERN' 'ARIZONA'
 'ARKANSAS, EASTERN' 'ARKANSAS, WESTERN' 'CALIFORNIA, CENTRAL'
 'CALIFORNIA, EASTERN' 'CALIFORNIA, NORTHERN' 'CALIFORNIA, SOUTHERN'
 'COLORADO' 'CONNECTICUT' 'DELAWARE' 'DISTRICT OF COLUMBIA'
 'FLORIDA, MIDDLE' 'FLORIDA, NORTHERN' 'FLORIDA, SOUTHERN'
 'GEORGIA, MIDDLE' 'GEORGIA, NORTHERN' 'GEORGIA, SOUTHERN' 'IDAHO'
 'ILLINOIS, CENTRAL' 'ILLINOIS, SOUTHERN' 'INDIANA, NORTHERN'
 'INDIANA, SOUTHERN' 'IOWA, NORTHERN' 'IOWA, SOUTHERN' 'KANSAS'
 'KENTUCKY, EASTERN' 'KENTUCKY, WESTERN' 'LOUISIANA, EASTERN'
 'LOUISIANA, MIDDLE' 'LOUISIANA, WESTERN' 'MAINE' 'MARYLAND'
 'MASSACHUSETTS' 'MICHIGAN, EASTERN' 'MICHIGAN, WESTERN' 'MINNESOTA'
 'MISSISSIPPI, NORTHERN' 'MISSISSIPPI, SOUTHERN' 'MISSOURI, EASTERN'
 'MISSOURI, WESTERN' 'MONTANA' 'NEBRASKA' 'NEVADA' 'NEW HAMPSHIRE'
 'NEW JERSEY' 'NEW MEXICO' 'NEW YORK, EASTERN' 'NEW YORK, NORTHERN'
 'NEW YORK, SOUTHERN' 'NEW YORK, WESTERN' 'NORTH CAROLINA, EASTERN'
 'NORTH CAROLINA, MIDDLE' 'NORTH CAROLINA, WESTERN' 'NORTH DAKOTA'
 'OHIO, NORTHERN' 'OHIO, SOUTHERN' 'OKLAHOMA, EASTERN'
 'OKLAHOMA, NORTHERN' 'OKLAHOMA, WESTERN' 'OREGON' 'PENNSYLVANIA, EASTERN'
 'PENNSYLVANIA, MIDDLE' 'PENNSYLVANIA, WESTERN' 'RHODE ISLAND'
 'SOUTH CAROLINA' 'SOUTH DAKOTA' 'TENNESSEE, EASTERN' 'TENNESSEE, MIDDLE'
 'TENNESSEE, WESTERN' 'TEXAS, EASTERN' 'TEXAS, NORTHERN' 'TEXAS, SOUTHERN'
 'TEXAS, WESTERN' 'UTAH' 'VERMONT' 'VIRGINIA, EASTERN' 'VIRGINIA, WESTERN'
 'WASHINGTON, EASTERN' 'WASHINGTON, WESTERN' 'WEST VIRGINIA, NORTHERN'
 'WEST VIRGINIA, SOUTHERN' 'WISCONSIN, EASTERN' 'WISCONSIN, WESTERN'
 'WYOMING' 'ILLINOIS, NORTHERN']
In [54]:
jur['DISTRICT'] = jur['DISTRICT'].str.replace('T,H', 'TH')
jur['DISTRICT'] = jur['DISTRICT'].str.replace('E,W', 'EW')
jur['DISTRICT'] = jur['DISTRICT'].str.replace('S,T', 'ST')
print(jur.DISTRICT.unique())
['ALABAMA, MIDDLE' 'ALABAMA, NORTHERN' 'ALABAMA, SOUTHERN' 'ARIZONA'
 'ARKANSAS, EASTERN' 'ARKANSAS, WESTERN' 'CALIFORNIA, CENTRAL'
 'CALIFORNIA, EASTERN' 'CALIFORNIA, NORTHERN' 'CALIFORNIA, SOUTHERN'
 'COLORADO' 'CONNECTICUT' 'DELAWARE' 'DISTRICT OF COLUMBIA'
 'FLORIDA, MIDDLE' 'FLORIDA, NORTHERN' 'FLORIDA, SOUTHERN'
 'GEORGIA, MIDDLE' 'GEORGIA, NORTHERN' 'GEORGIA, SOUTHERN' 'IDAHO'
 'ILLINOIS, CENTRAL' 'ILLINOIS, SOUTHERN' 'INDIANA, NORTHERN'
 'INDIANA, SOUTHERN' 'IOWA, NORTHERN' 'IOWA, SOUTHERN' 'KANSAS'
 'KENTUCKY, EASTERN' 'KENTUCKY, WESTERN' 'LOUISIANA, EASTERN'
 'LOUISIANA, MIDDLE' 'LOUISIANA, WESTERN' 'MAINE' 'MARYLAND'
 'MASSACHUSETTS' 'MICHIGAN, EASTERN' 'MICHIGAN, WESTERN' 'MINNESOTA'
 'MISSISSIPPI, NORTHERN' 'MISSISSIPPI, SOUTHERN' 'MISSOURI, EASTERN'
 'MISSOURI, WESTERN' 'MONTANA' 'NEBRASKA' 'NEVADA' 'NEW HAMPSHIRE'
 'NEW JERSEY' 'NEW MEXICO' 'NEW YORK, EASTERN' 'NEW YORK, NORTHERN'
 'NEW YORK, SOUTHERN' 'NEW YORK, WESTERN' 'NORTH CAROLINA, EASTERN'
 'NORTH CAROLINA, MIDDLE' 'NORTH CAROLINA, WESTERN' 'NORTH DAKOTA'
 'OHIO, NORTHERN' 'OHIO, SOUTHERN' 'OKLAHOMA, EASTERN'
 'OKLAHOMA, NORTHERN' 'OKLAHOMA, WESTERN' 'OREGON' 'PENNSYLVANIA, EASTERN'
 'PENNSYLVANIA, MIDDLE' 'PENNSYLVANIA, WESTERN' 'RHODE ISLAND'
 'SOUTH CAROLINA' 'SOUTH DAKOTA' 'TENNESSEE, EASTERN' 'TENNESSEE, MIDDLE'
 'TENNESSEE, WESTERN' 'TEXAS, EASTERN' 'TEXAS, NORTHERN' 'TEXAS, SOUTHERN'
 'TEXAS, WESTERN' 'UTAH' 'VERMONT' 'VIRGINIA, EASTERN' 'VIRGINIA, WESTERN'
 'WASHINGTON, EASTERN' 'WASHINGTON, WESTERN' 'WEST VIRGINIA, NORTHERN'
 'WEST VIRGINIA, SOUTHERN' 'WISCONSIN, EASTERN' 'WISCONSIN, WESTERN'
 'WYOMING' 'ILLINOIS, NORTHERN']
In [55]:
#drop records from fed17 for just contiguous US

fed_jur17 = fed17[fed17.Jurisdiction != 'ALASKA']
fed_jur17 = fed_jur17[fed_jur17.Jurisdiction != 'HAWAII']
fed_jur17 = fed_jur17[fed_jur17.Jurisdiction != 'RHODE ISLAND']
fed_jur17 = fed_jur17[fed_jur17.Jurisdiction != 'PUERTO RICO']
fed_jur17.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 2953 entries, 0 to 3026
Data columns (total 28 columns):
Jurisdiction                          2952 non-null object
A.O. Number                           2952 non-null object
Judge                                 2952 non-null object
Offense                               2950 non-null object
Type                                  2952 non-null object
Location                              2952 non-null object
Application Date                      2952 non-null datetime64[ns]
Original Order (Days)                 2945 non-null float64
Number of Extensions                  2952 non-null float64
Total Length (Days)                   2952 non-null float64
Installation                          2952 non-null object
Number of Days in Operation           1685 non-null float64
Avg. Intercepts Per Day               1657 non-null float64
Number of Persons Intercepts          1649 non-null object
Number of Intercepts                  1657 non-null object
Number of Incriminating Intercepts    1657 non-null object
Total Cost($)                         1549 non-null object
Other Than Manpower Cost ($)          1549 non-null object
Arrests                               1596 non-null float64
Trials                                1582 non-null float64
Motions Granted                       1584 non-null float64
Motions Denied                        1584 non-null float64
Motions Pending                       1584 non-null float64
Persons Convicted                     1582 non-null float64
Cost Related                          330 non-null object
Results Related                       501 non-null object
year                                  2952 non-null float64
month                                 2952 non-null float64
dtypes: datetime64[ns](1), float64(13), object(14)
memory usage: 669.0+ KB
In [56]:
jur_tap = pd.merge(fed_jur17, jur, left_on= 'Jurisdiction', right_on= 'DISTRICT')
In [57]:
jur_tap.head()
Out[57]:
Jurisdiction A.O. Number Judge Offense Type Location Application Date Original Order (Days) Number of Extensions Total Length (Days) ... FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
0 ALABAMA, MIDDLE 2017-1 ALBRITTON, III NARCOTICS WC D 2017-02-28 30.0 0.0 30.0 ... 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
1 ALABAMA, MIDDLE 2017-2 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 1.0 60.0 ... 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
2 ALABAMA, MIDDLE 2017-3 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-02-28 30.0 0.0 30.0 ... 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
3 ALABAMA, MIDDLE 2017-4 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-03-07 30.0 1.0 60.0 ... 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
4 ALABAMA, MIDDLE 2017-5 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 0.0 30.0 ... 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...

5 rows × 38 columns

In [58]:
jur_tap.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 2952 entries, 0 to 2951
Data columns (total 38 columns):
Jurisdiction                          2952 non-null object
A.O. Number                           2952 non-null object
Judge                                 2952 non-null object
Offense                               2950 non-null object
Type                                  2952 non-null object
Location                              2952 non-null object
Application Date                      2952 non-null datetime64[ns]
Original Order (Days)                 2945 non-null float64
Number of Extensions                  2952 non-null float64
Total Length (Days)                   2952 non-null float64
Installation                          2952 non-null object
Number of Days in Operation           1685 non-null float64
Avg. Intercepts Per Day               1657 non-null float64
Number of Persons Intercepts          1649 non-null object
Number of Intercepts                  1657 non-null object
Number of Incriminating Intercepts    1657 non-null object
Total Cost($)                         1549 non-null object
Other Than Manpower Cost ($)          1549 non-null object
Arrests                               1596 non-null float64
Trials                                1582 non-null float64
Motions Granted                       1584 non-null float64
Motions Denied                        1584 non-null float64
Motions Pending                       1584 non-null float64
Persons Convicted                     1582 non-null float64
Cost Related                          330 non-null object
Results Related                       501 non-null object
year                                  2952 non-null float64
month                                 2952 non-null float64
FID                                   2952 non-null int64
ABBR                                  2952 non-null object
DISTRICT                              2952 non-null object
SQ_Miles                              2952 non-null float64
Shape_Leng                            2952 non-null float64
Shape_Area                            2952 non-null float64
District_N                            2952 non-null object
Shape__Are                            2952 non-null float64
Shape__Len                            2952 non-null float64
geometry                              2952 non-null object
dtypes: datetime64[ns](1), float64(18), int64(1), object(18)
memory usage: 899.4+ KB
In [59]:
jur_tap = jur_tap.drop(columns='DISTRICT')
In [60]:
jur_tap = jur_tap.set_index('Jurisdiction')
In [61]:
jur_tap.head()
Out[61]:
A.O. Number Judge Offense Type Location Application Date Original Order (Days) Number of Extensions Total Length (Days) Installation ... month FID ABBR SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
Jurisdiction
ALABAMA, MIDDLE 2017-1 ALBRITTON, III NARCOTICS WC D 2017-02-28 30.0 0.0 30.0 INSTALLED AND USED ... 2.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
ALABAMA, MIDDLE 2017-2 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 1.0 60.0 NO PROSECUTOR REPORT ... 4.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
ALABAMA, MIDDLE 2017-3 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-02-28 30.0 0.0 30.0 NO PROSECUTOR REPORT ... 2.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
ALABAMA, MIDDLE 2017-4 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-03-07 30.0 1.0 60.0 NO PROSECUTOR REPORT ... 3.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
ALABAMA, MIDDLE 2017-5 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 0.0 30.0 NO PROSECUTOR REPORT ... 4.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...

5 rows × 36 columns

In [62]:
fed_count = pd.DataFrame(fed17['Jurisdiction'].value_counts())
In [63]:
fed_count = fed_count.reset_index()
In [64]:
fed_count = fed_count.rename(columns={"index": "Jurisdiction", "Jurisdiction": "Count"})
In [65]:
jur_count = pd.merge(fed_count, jur, left_on= 'Jurisdiction', right_on= 'DISTRICT')
In [66]:
jur_count.set_index('Jurisdiction')
Out[66]:
Count FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
Jurisdiction
CALIFORNIA, SOUTHERN 213 11 CAS CALIFORNIA, SOUTHERN 13029.776 8.844054 2.191518 9 3.239572e+10 1.029109e+06 (POLYGON ((-1217650.238149849 3777528.40702273...
TEXAS, SOUTHERN 189 78 TXS TEXAS, SOUTHERN 103168.160 44.860009 10.328502 5 1.456691e+11 5.294942e+06 (POLYGON ((678392.5435317336 2877972.745050528...
CALIFORNIA, CENTRAL 175 8 CAC CALIFORNIA, CENTRAL 77737.552 26.141797 10.235889 9 1.540900e+11 3.109362e+06 (POLYGON ((-1341965.065576056 3830213.72658085...
ARIZONA 135 5 AZ ARIZONA 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
ILLINOIS, NORTHERN 131 92 ILN ILLINOIS, NORTHERN 0.000 9.641309 3.073970 7 5.117535e+10 1.183730e+06 POLYGON ((1455374.792921589 4682088.144150389,...
NEW YORK, SOUTHERN 107 54 NYS NEW YORK, SOUTHERN 3639.209 6.869691 1.018077 2 1.684980e+10 8.856330e+05 POLYGON ((2578481.171528419 4974270.815526023,...
COLORADO 94 12 CO COLORADO 104058.357 22.031328 28.054360 10 4.475472e+11 2.708929e+06 POLYGON ((67657.0177356624 4551709.815303136, ...
TEXAS, WESTERN 91 79 TXW TEXAS, WESTERN 91471.346 41.498785 22.251636 5 3.202545e+11 4.905598e+06 POLYGON ((247193.2798367047 3601848.732447769,...
MISSOURI, EASTERN 82 44 MOE MISSOURI, EASTERN 28965.802 20.862432 7.734540 8 1.222138e+11 2.600400e+06 POLYGON ((1118841.968231897 4518488.104746007,...
MARYLAND 66 37 MD MARYLAND 25423.939 46.487592 2.642988 4 4.218177e+10 5.830967e+06 (POLYGON ((2542526.451851783 4456971.980688113...
NEW JERSEY 63 50 NJ NEW JERSEY 18496.658 14.275519 2.093558 3 3.396659e+10 1.815887e+06 (POLYGON ((2620013.593655886 4647184.434409566...
FLORIDA, SOUTHERN 59 18 FLS FLORIDA, SOUTHERN 164355.334 26.857592 2.399556 11 3.323875e+10 3.173552e+06 (POLYGON ((2243535.128965369 2821581.791515818...
CONNECTICUT 53 13 CT CONNECTICUT 5657.781 7.065003 1.393128 2 2.309454e+10 9.026114e+05 (POLYGON ((2770162.898902574 4945058.499139016...
LOUISIANA, EASTERN 52 33 LAE LOUISIANA, EASTERN 254698.639 46.782856 2.219269 5 3.173760e+10 5.548830e+06 (POLYGON ((1290608.985036151 3240439.46978122,...
OHIO, NORTHERN 52 60 OHN OHIO, NORTHERN 19193.418 15.179603 5.037833 6 8.281001e+10 1.889106e+06 (POLYGON ((1860518.31583836 4737544.036392632,...
INDIANA, SOUTHERN 50 27 INS INDIANA, SOUTHERN 22713.272 14.576477 6.141331 7 9.824338e+10 1.841569e+06 POLYGON ((1613413.384194463 4574452.409999062,...
GEORGIA, NORTHERN 48 20 GAN GEORGIA, NORTHERN 14484.294 10.058370 3.664506 11 5.484908e+10 1.214110e+06 POLYGON ((1957187.794888234 3989419.325908749,...
OKLAHOMA, WESTERN 48 64 OKW OKLAHOMA, WESTERN 39458.452 20.766551 10.191407 10 1.557325e+11 2.489025e+06 POLYGON ((499951.8033027247 4094957.005900142,...
FLORIDA, MIDDLE 46 16 FLM FLORIDA, MIDDLE 165833.265 52.002268 6.976722 11 9.832156e+10 6.224150e+06 (POLYGON ((2276135.892697865 2974845.523571447...
UTAH 45 80 UT UTAH 84800.868 19.998615 22.969884 10 3.682100e+11 2.556782e+06 POLYGON ((-537583.857536994 4726578.586626041,...
PENNSYLVANIA, WESTERN 45 68 PAW PENNSYLVANIA, WESTERN 19168.855 11.336522 5.311254 3 8.706643e+10 1.440796e+06 POLYGON ((2088917.995722735 4862560.459365817,...
DISTRICT OF COLUMBIA 44 15 DC DISTRICT OF COLUMBIA 60.158 0.716060 0.016194 District of Columbia 2.579056e+08 8.913811e+04 POLYGON ((2422537.36391487 4545973.8173753, 24...
NEW YORK, EASTERN 43 52 NYE NEW YORK, EASTERN 8513.415 13.480205 0.400256 2 6.554026e+09 1.637111e+06 (POLYGON ((2638968.282054695 4811813.780978137...
SOUTH CAROLINA 40 71 SC SOUTH CAROLINA 50609.140 24.276642 7.802899 4 1.165366e+11 2.919215e+06 (POLYGON ((2231859.420064836 3724753.938132987...
NEW MEXICO 39 51 NM NEW MEXICO 125312.924 23.673530 30.887868 10 4.643178e+11 2.898964e+06 POLYGON ((-229451.5855475657 4126270.229180015...
NEW YORK, NORTHERN 38 53 NYN NEW YORK, NORTHERN 50116.123 17.882899 9.045756 2 1.541550e+11 2.316827e+06 (POLYGON ((2322052.346830436 5115957.994043431...
ARKANSAS, EASTERN 37 6 ARE ARKANSAS, EASTERN 28875.088 20.282134 7.391100 8 1.119305e+11 2.444057e+06 POLYGON ((1245497.465320354 4071327.816416644,...
CALIFORNIA, NORTHERN 37 10 CAN CALIFORNIA, NORTHERN 26734.821 29.120485 5.579357 9 8.854351e+10 3.691538e+06 (POLYGON ((-1629578.215244076 4454734.98264079...
KANSAS 34 30 KS KANSAS 82228.102 21.349084 22.007447 10 3.484898e+11 2.561317e+06 POLYGON ((398031.8593221063 4428747.136296699,...
OHIO, SOUTHERN 34 61 OHS OHIO, SOUTHERN 23131.694 14.042997 6.295082 6 1.013534e+11 1.761304e+06 POLYGON ((1885220.966742389 4631763.113647775,...
... ... ... ... ... ... ... ... ... ... ... ...
WISCONSIN, EASTERN 15 89 WIE WISCONSIN, EASTERN 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
ALABAMA, NORTHERN 15 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
RHODE ISLAND 14 70 RI RHODE ISLAND 1896.307 5.895388 0.306881 1 5.091440e+09 7.839080e+05 (POLYGON ((2810096.154486801 4938827.269533045...
WEST VIRGINIA, SOUTHERN 14 88 WVS WEST VIRGINIA, SOUTHERN 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
MICHIGAN, WESTERN 13 40 MIW MICHIGAN, WESTERN 202286.736 56.110728 10.740892 6 1.877354e+11 7.314555e+06 (POLYGON ((1517814.710087565 5068459.026910815...
KENTUCKY, EASTERN 13 31 KYE KENTUCKY, EASTERN 20552.690 13.002380 5.447175 6 8.541071e+10 1.590641e+06 POLYGON ((1746192.490879328 4424648.461906386,...
NORTH CAROLINA, EASTERN 13 56 NCE NORTH CAROLINA, EASTERN 105271.204 53.719795 6.220372 4 9.458974e+10 6.556103e+06 (POLYGON ((2407746.176870722 3940889.887875024...
NEW YORK, WESTERN 12 55 NYW NEW YORK, WESTERN 13031.048 10.213668 3.411014 2 5.743645e+10 1.251244e+06 (POLYGON ((2134337.150047214 4967548.405485432...
LOUISIANA, MIDDLE 11 34 LAM LOUISIANA, MIDDLE 4649.808 4.744406 1.026801 5 1.477856e+10 5.625574e+05 POLYGON ((1196506.215253713 3457618.182638578,...
MINNESOTA 11 41 MN MINNESOTA 90887.848 32.681448 25.537954 8 4.587720e+11 4.204464e+06 (POLYGON ((1027553.952790932 5203263.489648597...
TENNESSEE, MIDDLE 11 74 TNM TENNESSEE, MIDDLE 14297.618 11.867564 3.702203 6 5.671259e+10 1.417736e+06 POLYGON ((1498172.566152157 4116147.478395873,...
TEXAS, EASTERN 11 76 TXE TEXAS, EASTERN 33775.112 23.679032 8.354699 5 1.222746e+11 2.877492e+06 POLYGON ((854390.4930180375 3761874.538346393,...
ILLINOIS, SOUTHERN 9 25 ILS ILLINOIS, SOUTHERN 16652.942 14.690890 4.450294 7 7.036096e+10 1.820592e+06 POLYGON ((1474210.560531489 4432606.679928833,...
OKLAHOMA, NORTHERN 9 63 OKN OKLAHOMA, NORTHERN 8907.036 8.140655 2.320193 10 3.575436e+10 9.838026e+05 POLYGON ((809362.1916551577 4100627.833549995,...
NEW HAMPSHIRE 8 49 NH NEW HAMPSHIRE 9242.031 9.494378 2.677472 1 4.589230e+10 1.275454e+06 POLYGON ((2684409.083811051 5411159.390295265,...
GEORGIA, MIDDLE 7 19 GAM GEORGIA, MIDDLE 25146.547 17.223128 6.224920 11 9.116960e+10 2.067174e+06 POLYGON ((1965794.615244794 3938419.85201234, ...
MISSISSIPPI, NORTHERN 6 42 MSN MISSISSIPPI, NORTHERN 20045.319 14.689317 5.064504 5 7.569719e+10 1.768864e+06 POLYGON ((1486493.205863159 3926439.335272078,...
MONTANA 6 46 MT MONTANA 146692.221 35.399658 45.092607 9 8.204490e+11 4.447733e+06 POLYGON ((-56087.64181823551 5455591.54431823,...
ALABAMA, MIDDLE 6 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
FLORIDA, NORTHERN 5 17 FLN FLORIDA, NORTHERN 80851.513 31.026591 4.136558 11 5.935770e+10 3.632281e+06 (POLYGON ((2093082.81187928 3318807.819295, 20...
DELAWARE 5 14 DE DELAWARE 2048.253 5.457967 0.552171 4 8.806052e+09 7.055051e+05 POLYGON ((2525012.190007585 4671140.936623267,...
WEST VIRGINIA, NORTHERN 4 87 WVN WEST VIRGINIA, NORTHERN 12727.747 14.858897 3.435725 4 5.486757e+10 1.883769e+06 POLYGON ((2066524.575578697 4666331.385270311,...
MAINE 4 36 ME MAINE 246044.455 45.429128 9.662571 1 1.705500e+11 6.144935e+06 (POLYGON ((2826098.482538667 5241453.982792264...
WISCONSIN, WESTERN 4 90 WIW WISCONSIN, WESTERN 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...
ALABAMA, SOUTHERN 4 3 ALS ALABAMA, SOUTHERN 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
VIRGINIA, WESTERN 4 84 VAW VIRGINIA, WESTERN 24622.384 19.886863 6.503469 4 1.015817e+11 2.378741e+06 POLYGON ((2336045.461008049 4548146.570400048,...
WASHINGTON, EASTERN 3 85 WAE WASHINGTON, EASTERN 42339.553 18.033116 13.098106 9 2.397890e+11 2.338462e+06 POLYGON ((-1092380.909345797 5662104.909743664...
ILLINOIS, CENTRAL 2 24 ILC ILLINOIS, CENTRAL 28739.027 14.305483 7.894371 7 1.282952e+11 1.808202e+06 POLYGON ((1227770.955034963 4661652.090511092,...
IDAHO 1 23 ID IDAHO 83394.626 29.686713 24.456865 9 4.246831e+11 4.001904e+06 POLYGON ((-771178.8379696188 5575608.381396202...
IOWA, SOUTHERN 1 29 IAS IOWA, SOUTHERN 25252.726 17.607201 7.042830 8 1.162024e+11 2.139765e+06 POLYGON ((1014456.124073487 4691939.629893651,...

83 rows × 11 columns

In [67]:
jur_count.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 83 entries, 0 to 82
Data columns (total 12 columns):
Jurisdiction    83 non-null object
Count           83 non-null int64
FID             83 non-null int64
ABBR            83 non-null object
DISTRICT        83 non-null object
SQ_Miles        83 non-null float64
Shape_Leng      83 non-null float64
Shape_Area      83 non-null float64
District_N      83 non-null object
Shape__Are      83 non-null float64
Shape__Len      83 non-null float64
geometry        83 non-null object
dtypes: float64(5), int64(2), object(5)
memory usage: 8.4+ KB
In [68]:
jur_count = gpd.GeoDataFrame(jur_count)
In [69]:
jur_count.plot(column='Count')
Out[69]:
<matplotlib.axes._subplots.AxesSubplot at 0x1a1ac16c50>
In [70]:
#Add gradient legend
from matplotlib.colors import Normalize
from matplotlib import cm

#Set up figure and axis with different size
f, ax = plt.subplots(1, figsize=(15,15))
#Remove axis
ax.set_axis_off()
#Set title 
ax.set_title('Wiretaps per Federal Jurisdiction, 2017', fontsize=25)
#Add Basemap to axix
jur.plot(ax=ax, color='white', edgecolor='grey')
#Add second layer of polygons on the axis 
jur_count.plot(column='Count', ax=ax, legend=False)

#Add legend
mn = jur_count.Count.min()
mx = jur_count.Count.max()
norm = Normalize(vmin=mn, vmax=mx)
n_cmap = cm.ScalarMappable(norm=norm, cmap="viridis")
n_cmap.set_array([])
ax.get_figure().colorbar(n_cmap, ax=ax, orientation='vertical', shrink=.5)
Out[70]:
<matplotlib.colorbar.Colorbar at 0x1a24e47a58>

We can see that jurisdictions with the lots of wiretaps are in Southern California, Texas, and Arizona. Chicago also has a high number of taps. We should take a closer look at these jurisdiction. Los Angeles and Texas would be particularly interesting due to their proximity to the border. It seems likely that wiretaps there would be related to transnational drug trafficking investigations.

Look at number of wiretaps per crime per jurisdiction

In [71]:
jur_tap.reset_index()
Out[71]:
Jurisdiction A.O. Number Judge Offense Type Location Application Date Original Order (Days) Number of Extensions Total Length (Days) ... month FID ABBR SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
0 ALABAMA, MIDDLE 2017-1 ALBRITTON, III NARCOTICS WC D 2017-02-28 30.0 0.0 30.0 ... 2.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
1 ALABAMA, MIDDLE 2017-2 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 1.0 60.0 ... 4.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
2 ALABAMA, MIDDLE 2017-3 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-02-28 30.0 0.0 30.0 ... 2.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
3 ALABAMA, MIDDLE 2017-4 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-03-07 30.0 1.0 60.0 ... 3.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
4 ALABAMA, MIDDLE 2017-5 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-04-05 30.0 0.0 30.0 ... 4.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
5 ALABAMA, MIDDLE 2017-6 ALBRITTON, III DRUGS - ILLEGAL WC,TX D 2017-02-06 30.0 1.0 60.0 ... 2.0 1 ALM 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
6 ALABAMA, NORTHERN 2015-7 HOPKINS NARCOTICS WC D 2015-02-18 30.0 0.0 30.0 ... 2.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
7 ALABAMA, NORTHERN 2015-8 PROCTOR NARCOTICS WC D 2015-07-27 30.0 0.0 30.0 ... 7.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
8 ALABAMA, NORTHERN 2016-17 HAIKALA NARCOTICS WC D 2016-04-18 30.0 0.0 30.0 ... 4.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
9 ALABAMA, NORTHERN 2016-18 BOWDRE NARCOTICS WC,TX D 2016-05-23 30.0 0.0 30.0 ... 5.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
10 ALABAMA, NORTHERN 2016-19 HAIKALA NARCOTICS WC D 2016-06-09 30.0 0.0 30.0 ... 6.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
11 ALABAMA, NORTHERN 2016-20 HAIKALA NARCOTICS WC D 2016-06-20 30.0 0.0 30.0 ... 6.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
12 ALABAMA, NORTHERN 2017-7 BOWDRE DRUGS - ILLEGAL WC D 2017-05-10 30.0 0.0 30.0 ... 5.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
13 ALABAMA, NORTHERN 2017-8 COOGLER DRUGS - ILLEGAL WC,TX D 2017-06-20 8.0 0.0 8.0 ... 6.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
14 ALABAMA, NORTHERN 2017-9 HAIKALA DRUGS - ILLEGAL WC,TX D 2017-04-24 30.0 1.0 60.0 ... 4.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
15 ALABAMA, NORTHERN 2017-10 HOPKINS CONSPIRACY WC,OM,TX D 2017-02-10 18.0 0.0 18.0 ... 2.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
16 ALABAMA, NORTHERN 2017-11 HOPKINS DRUGS - ILLEGAL WC,TX D,R 2017-08-22 30.0 0.0 30.0 ... 8.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
17 ALABAMA, NORTHERN 2017-12 BOWDRE DRUGS - ILLEGAL WC D 2017-05-17 30.0 0.0 30.0 ... 5.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
18 ALABAMA, NORTHERN 2017-13 HOPKINS DRUGS - ILLEGAL WC D 2017-08-10 30.0 0.0 30.0 ... 8.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
19 ALABAMA, NORTHERN 2017-14 BOWDRE DRUGS - ILLEGAL WC D 2017-05-17 30.0 0.0 30.0 ... 5.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
20 ALABAMA, NORTHERN 2017-15 KALLON DRUGS - ILLEGAL WC,TX D 2017-05-25 26.0 0.0 26.0 ... 5.0 2 ALN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
21 ALABAMA, SOUTHERN 2016-19 DUBOSE NARCOTICS WC,TX D 2016-04-25 30.0 0.0 30.0 ... 4.0 3 ALS 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
22 ALABAMA, SOUTHERN 2017-2 STEELE DRUGS - ILLEGAL WC,TX D 2017-06-28 30.0 0.0 30.0 ... 6.0 3 ALS 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
23 ALABAMA, SOUTHERN 2017-3 STEELE DRUGS - ILLEGAL WC,TX D 2017-05-25 30.0 1.0 60.0 ... 5.0 3 ALS 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
24 ALABAMA, SOUTHERN 2017-4 DUBOSE DRUGS - ILLEGAL WC,TX D 2017-03-06 30.0 1.0 60.0 ... 3.0 3 ALS 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
25 ARIZONA 2014-217 COLLINS NARCOTICS TX D 2014-03-09 30.0 0.0 30.0 ... 3.0 5 AZ 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
26 ARIZONA 2015-283 ZAPATA NARCOTICS EO D 2015-02-11 30.0 0.0 30.0 ... 2.0 5 AZ 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
27 ARIZONA 2015-284 ZAPATA NARCOTICS EO D 2015-03-12 30.0 0.0 30.0 ... 3.0 5 AZ 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
28 ARIZONA 2015-285 LOGAN FRAUD EO B 2015-08-19 30.0 2.0 90.0 ... 8.0 5 AZ 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
29 ARIZONA 2016-351 HUMETEWA NARCOTICS WC,TX D 2016-02-19 30.0 0.0 30.0 ... 2.0 5 AZ 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2922 WEST VIRGINIA, SOUTHERN 2017-4 CHAMBERS NARCOTICS WC,EO D 2017-07-07 30.0 4.0 150.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2923 WEST VIRGINIA, SOUTHERN 2017-5 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-04-04 30.0 0.0 30.0 ... 4.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2924 WEST VIRGINIA, SOUTHERN 2017-6 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-07-07 30.0 1.0 60.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2925 WEST VIRGINIA, SOUTHERN 2017-7 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-04-04 30.0 2.0 90.0 ... 4.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2926 WEST VIRGINIA, SOUTHERN 2017-8 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-07-07 30.0 2.0 90.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2927 WEST VIRGINIA, SOUTHERN 2017-9 CHAMBERS DRUGS - ILLEGAL WC D 2017-04-27 30.0 0.0 30.0 ... 4.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2928 WEST VIRGINIA, SOUTHERN 2017-10 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-07-07 30.0 4.0 150.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2929 WEST VIRGINIA, SOUTHERN 2017-11 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-07-07 30.0 0.0 30.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2930 WEST VIRGINIA, SOUTHERN 2017-12 CHAMBERS DRUGS - ILLEGAL WC,WS,TX D,H 2017-04-04 30.0 1.0 60.0 ... 4.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2931 WEST VIRGINIA, SOUTHERN 2017-13 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-04-04 30.0 3.0 120.0 ... 4.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2932 WEST VIRGINIA, SOUTHERN 2017-14 CHAMBERS DRUGS - ILLEGAL WC,TX D 2017-07-07 30.0 3.0 120.0 ... 7.0 88 WVS 11458.626 10.580239 3.051338 4 4.807060e+10 1.317327e+06 POLYGON ((2024166.95854979 4504724.185931213, ...
2933 WISCONSIN, EASTERN 2017-1 ADELMAN NARCOTICS WC,TX D 2016-12-02 30.0 1.0 60.0 ... 12.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2934 WISCONSIN, EASTERN 2017-2 ADELMAN NARCOTICS WC,TX D 2016-12-29 30.0 0.0 30.0 ... 12.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2935 WISCONSIN, EASTERN 2017-3 PEPPER NARCOTICS WC D 2017-03-17 30.0 0.0 30.0 ... 3.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2936 WISCONSIN, EASTERN 2017-4 ADELMAN NARCOTICS WC,TX D 2017-03-02 30.0 1.0 60.0 ... 3.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2937 WISCONSIN, EASTERN 2017-5 ADELMAN NARCOTICS WC,TX D 2017-04-06 30.0 0.0 30.0 ... 4.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2938 WISCONSIN, EASTERN 2017-6 PEPPER NARCOTICS WC D 2017-05-05 30.0 0.0 30.0 ... 5.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2939 WISCONSIN, EASTERN 2017-7 ADELMAN NARCOTICS WC,TX D 2017-06-08 30.0 1.0 60.0 ... 6.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2940 WISCONSIN, EASTERN 2017-8 ADELMAN NARCOTICS WC,TX D 2017-07-07 30.0 1.0 60.0 ... 7.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2941 WISCONSIN, EASTERN 2017-9 ADELMAN NARCOTICS WC,TX D 2017-05-11 30.0 3.0 120.0 ... 5.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2942 WISCONSIN, EASTERN 2017-10 ADELMAN NARCOTICS WC,TX D 2017-08-04 30.0 1.0 60.0 ... 8.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2943 WISCONSIN, EASTERN 2017-11 ADELMAN CONSPIRACY WC,TX D 2016-12-02 30.0 11.0 360.0 ... 12.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2944 WISCONSIN, EASTERN 2017-12 STADTMUELLER CONSPIRACY WC,TX D 2017-03-30 30.0 8.0 270.0 ... 3.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2945 WISCONSIN, EASTERN 2017-13 PEPPER CONSPIRACY WC D 2017-05-05 30.0 0.0 30.0 ... 5.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2946 WISCONSIN, EASTERN 2017-14 PEPPER CONSPIRACY WC D 2017-03-17 30.0 0.0 30.0 ... 3.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2947 WISCONSIN, EASTERN 2017-15 ADELMAN CONSPIRACY WC,TX D 2017-09-12 30.0 0.0 30.0 ... 9.0 89 WIE 24567.673 17.771083 4.925495 7 8.539704e+10 2.397405e+06 (POLYGON ((1406403.267505941 5053956.145840753...
2948 WISCONSIN, WESTERN 2017-1 CONLEY NARCOTICS WC,TX D 2016-12-15 30.0 0.0 30.0 ... 12.0 90 WIW 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...
2949 WISCONSIN, WESTERN 2017-2 CONLEY NARCOTICS WC,TX D 2017-01-13 30.0 0.0 30.0 ... 1.0 90 WIW 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...
2950 WISCONSIN, WESTERN 2017-3 CONLEY CONSPIRACY WC D 2016-12-16 30.0 0.0 30.0 ... 12.0 90 WIW 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...
2951 WISCONSIN, WESTERN 2017-4 CONLEY CONSPIRACY WC D 2017-01-13 30.0 0.0 30.0 ... 1.0 90 WIW 63014.440 22.570273 11.542167 7 2.015481e+11 2.956760e+06 (POLYGON ((1127270.283915726 5207598.399092081...

2952 rows × 37 columns

In [72]:
jt = pd.DataFrame(jur_tap.groupby('Jurisdiction').Offense.value_counts())
jt.head()
Out[72]:
Offense
Jurisdiction Offense
ALABAMA, MIDDLE DRUGS - ILLEGAL 5
NARCOTICS 1
ALABAMA, NORTHERN DRUGS - ILLEGAL 8
NARCOTICS 6
CONSPIRACY 1
In [73]:
jt = jt.rename(columns={'Offense': 'Count'})
jt = jt.reset_index()
jt = pd.merge(jt, jur, left_on= 'Jurisdiction', right_on= 'DISTRICT')
jt.head()
Out[73]:
Jurisdiction Offense Count FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
0 ALABAMA, MIDDLE DRUGS - ILLEGAL 5 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
1 ALABAMA, MIDDLE NARCOTICS 1 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
2 ALABAMA, NORTHERN DRUGS - ILLEGAL 8 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
3 ALABAMA, NORTHERN NARCOTICS 6 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
4 ALABAMA, NORTHERN CONSPIRACY 1 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
In [74]:
jt = gpd.GeoDataFrame(jt)
In [75]:
#Plot the Count per jurisdiction for a specific Offense

def off_count(df=jt, offense='Offense'):
    
    d = jt.loc[jt['Offense'] == offense]

    #Set up figure and axis with different size
    f, ax = plt.subplots(1, figsize=(15,15))
    #Remove axis
    ax.set_axis_off()
    #Set title 
    ax.set_title('2017 Wiretaps for Crime Type: ' + offense, fontsize=25)
    #Add baselayer
    jur.plot(ax=ax, color='white', edgecolor='grey')
    #Add second layer of polygons on the axis
    d.plot(column='Count', ax=ax, legend=False)
    #Add legend
    mn = d.Count.min()
    mx = d.Count.max()
    norm = Normalize(vmin=mn, vmax=mx)
    n_cmap = cm.ScalarMappable(norm=norm, cmap="viridis")
    n_cmap.set_array([])
    ax.get_figure().colorbar(n_cmap, ax=ax, orientation='vertical', shrink=.5)
    #Display
    plt.show()
In [76]:
off_count(offense='NARCOTICS')
In [77]:
jt.Offense.unique()
Out[77]:
array(['DRUGS - ILLEGAL', 'NARCOTICS', 'CONSPIRACY', 'OTHER',
       'IMMIGRATION', 'FRAUD', 'SMUGGLING', 'RACKETEERING', 'BRIBERY',
       'POSSESSION', 'USURY', 'FIREARMS', 'LOANSHARKING', '$LAUNDERING',
       'CORRUPTION', 'DRUGS - PRESCRIPTION', 'TERRORISM', 'GAMBLING',
       'BOMBING', 'CIVIL RIGHTS', 'MURDER', 'KIDNAPPING', 'ROBBERY',
       'ASSAULT', 'EXTORTION', 'EMBEZZLEMENT', 'COERCION', 'THEFT'],
      dtype=object)
In [78]:
off_count(offense='DRUGS - ILLEGAL')

Note: Should combine narcotics and drugs - illegal for final report

In [79]:
off_count(offense='CORRUPTION')
In [80]:
off_count(offense='CONSPIRACY')
In [81]:
off_count(offense='RACKETEERING')
In [82]:
off_count(offense='IMMIGRATION')

Interesting that Arizona is the only Federal Jurisdiction ordering wiretaps for the "crime" of immigration. This is not surprising given the state's aggressive law enforcement approach to illegal immigration. See pages 40-44 of Karla McKanders (2017) "The Subnational Response: Local Intervention in Immigration Policy and Enforcement" in Steven Bender and William Arrocha (eds.), Compassionate Migration and Regional Policy in the Americas, New York: Palgrave Macmillan.

In [83]:
off_count(offense='TERRORISM')
In [84]:
off_count(offense='SMUGGLING')

See comment above regarding immigration policy in Arizona. Wiretaps for the "crime" of smuggling may be related to US legislative efforts to criminalize immigrants by framing immigration as a form of human smuggling. See p. 10 of Susan Martin (2013), "US Immigration Reform," James A. Baker III Institute for Public Policy.

In [85]:
off_count(offense='LOANSHARKING')
In [86]:
off_count(offense='DRUGS - PRESCRIPTION')
In [87]:
off_count(offense='$LAUNDERING')
In [88]:
off_count(offense='CORRUPTION')
In [89]:
off_count(offense='GAMBLING')
In [90]:
off_count(offense='FRAUD')
In [91]:
off_count(offense='EMBEZZLEMENT')
In [92]:
off_count(offense='KIDNAPPING')
In [93]:
off_count(offense='CONSPIRACY')
In [94]:
off_count(offense='EXTORTION')
In [95]:
off_count(offense='BRIBERY')
In [96]:
off_count(offense='MURDER')
In [97]:
off_count(offense='ROBBERY')
In [115]:
off_count(offense='OTHER')

look at only taps with prosecutor report

In [98]:
j = jur_tap.loc[jur_tap['Installation'] == 'INSTALLED AND USED']
In [99]:
j.reset_index(inplace=True)
In [100]:
j.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1692 entries, 0 to 1691
Data columns (total 37 columns):
Jurisdiction                          1692 non-null object
A.O. Number                           1692 non-null object
Judge                                 1692 non-null object
Offense                               1692 non-null object
Type                                  1692 non-null object
Location                              1692 non-null object
Application Date                      1692 non-null datetime64[ns]
Original Order (Days)                 1692 non-null float64
Number of Extensions                  1692 non-null float64
Total Length (Days)                   1692 non-null float64
Installation                          1692 non-null object
Number of Days in Operation           1685 non-null float64
Avg. Intercepts Per Day               1657 non-null float64
Number of Persons Intercepts          1649 non-null object
Number of Intercepts                  1657 non-null object
Number of Incriminating Intercepts    1657 non-null object
Total Cost($)                         1549 non-null object
Other Than Manpower Cost ($)          1549 non-null object
Arrests                               1596 non-null float64
Trials                                1582 non-null float64
Motions Granted                       1584 non-null float64
Motions Denied                        1584 non-null float64
Motions Pending                       1584 non-null float64
Persons Convicted                     1582 non-null float64
Cost Related                          319 non-null object
Results Related                       481 non-null object
year                                  1692 non-null float64
month                                 1692 non-null float64
FID                                   1692 non-null int64
ABBR                                  1692 non-null object
SQ_Miles                              1692 non-null float64
Shape_Leng                            1692 non-null float64
Shape_Area                            1692 non-null float64
District_N                            1692 non-null object
Shape__Are                            1692 non-null float64
Shape__Len                            1692 non-null float64
geometry                              1692 non-null object
dtypes: datetime64[ns](1), float64(18), int64(1), object(17)
memory usage: 489.2+ KB
In [101]:
pd.options.mode.chained_assignment = None

look at wiretaps by cost

In [102]:
j.dropna(subset=['Total Cost($)'], inplace=True)
In [103]:
j['Cost'] = j['Total Cost($)'].str.replace(',','')
In [104]:
j['Cost'] = pd.to_numeric(j['Cost'])
In [105]:
#How many observations have a cost of 0?
j.loc[j['Cost'] == 0].describe()
Out[105]:
Original Order (Days) Number of Extensions Total Length (Days) Number of Days in Operation Avg. Intercepts Per Day Arrests Trials Motions Granted Motions Denied Motions Pending Persons Convicted year month FID SQ_Miles Shape_Leng Shape_Area Shape__Are Shape__Len Cost
count 212.0 212.000000 212.000000 212.000000 212.000000 212.000000 212.000000 212.000000 212.000000 212.0 212.000000 212.000000 212.000000 212.000000 212.000000 212.000000 212.000000 2.120000e+02 2.120000e+02 212.0
mean 30.0 0.627358 48.820755 46.301887 67.004717 0.117925 0.004717 0.188679 0.117925 0.0 0.117925 2016.339623 6.985849 44.476415 41160.988316 19.366565 7.719140 1.217493e+11 2.381211e+06 0.0
std 0.0 1.557068 46.712045 47.113591 99.803575 1.717007 0.068680 2.747211 1.717007 0.0 1.717007 1.283411 3.241436 25.271875 44563.862115 12.490072 7.562906 1.192036e+11 1.511862e+06 0.0
min 30.0 0.000000 30.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.0 0.000000 2010.000000 1.000000 2.000000 0.000000 0.716060 0.016194 2.579056e+08 8.913811e+04 0.0
25% 30.0 0.000000 30.000000 30.000000 4.000000 0.000000 0.000000 0.000000 0.000000 0.0 0.000000 2016.000000 4.000000 18.000000 11070.328500 9.641309 2.642988 4.218177e+10 1.183730e+06 0.0
50% 30.0 0.000000 30.000000 30.000000 24.500000 0.000000 0.000000 0.000000 0.000000 0.0 0.000000 2017.000000 7.000000 48.500000 22713.272000 15.484555 5.379215 8.706643e+10 1.901579e+06 0.0
75% 30.0 1.000000 60.000000 60.000000 98.250000 0.000000 0.000000 0.000000 0.000000 0.0 0.000000 2017.000000 10.000000 63.250000 50239.377250 22.031328 10.191407 1.540900e+11 2.708929e+06 0.0
max 30.0 16.000000 510.000000 510.000000 596.000000 25.000000 1.000000 40.000000 25.000000 0.0 25.000000 2017.000000 12.000000 92.000000 254698.639000 53.719795 28.930993 4.475472e+11 6.556103e+06 0.0

212 observations list a cost of 0. Why might this be? For some cases, perhaps the wiretaps was never deployed. However, we can see from the table above that others were definitely used (see the max values for 'number of extensions,' 'arrests,' etc). In these cases, perhaps the total cost was not yet recorded because the operation was ongoing. Regardless, we will have to drop observations with costs of 0 because they will skew the mean cost.

In [106]:
#drop values where cost is 0: these were likely not deployed at all
j = j[j['Cost'] != 0]
In [107]:
j.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1337 entries, 0 to 1691
Data columns (total 38 columns):
Jurisdiction                          1337 non-null object
A.O. Number                           1337 non-null object
Judge                                 1337 non-null object
Offense                               1337 non-null object
Type                                  1337 non-null object
Location                              1337 non-null object
Application Date                      1337 non-null datetime64[ns]
Original Order (Days)                 1337 non-null float64
Number of Extensions                  1337 non-null float64
Total Length (Days)                   1337 non-null float64
Installation                          1337 non-null object
Number of Days in Operation           1337 non-null float64
Avg. Intercepts Per Day               1319 non-null float64
Number of Persons Intercepts          1311 non-null object
Number of Intercepts                  1319 non-null object
Number of Incriminating Intercepts    1319 non-null object
Total Cost($)                         1337 non-null object
Other Than Manpower Cost ($)          1337 non-null object
Arrests                               1291 non-null float64
Trials                                1278 non-null float64
Motions Granted                       1280 non-null float64
Motions Denied                        1280 non-null float64
Motions Pending                       1280 non-null float64
Persons Convicted                     1278 non-null float64
Cost Related                          15 non-null object
Results Related                       253 non-null object
year                                  1337 non-null float64
month                                 1337 non-null float64
FID                                   1337 non-null int64
ABBR                                  1337 non-null object
SQ_Miles                              1337 non-null float64
Shape_Leng                            1337 non-null float64
Shape_Area                            1337 non-null float64
District_N                            1337 non-null object
Shape__Are                            1337 non-null float64
Shape__Len                            1337 non-null float64
geometry                              1337 non-null object
Cost                                  1337 non-null int64
dtypes: datetime64[ns](1), float64(18), int64(2), object(17)
memory usage: 407.4+ KB
In [108]:
p = pd.DataFrame(j.groupby('Jurisdiction')['Cost'].mean())
p.head()
Out[108]:
Cost
Jurisdiction
ALABAMA, MIDDLE 269859.000000
ALABAMA, NORTHERN 67802.000000
ALABAMA, SOUTHERN 26392.000000
ARIZONA 109178.291667
ARKANSAS, EASTERN 33835.000000
In [109]:
p = p.reset_index()
In [110]:
p = pd.merge(p, jur, left_on= 'Jurisdiction', right_on= 'DISTRICT')
p.head()
Out[110]:
Jurisdiction Cost FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
0 ALABAMA, MIDDLE 269859.000000 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
1 ALABAMA, NORTHERN 67802.000000 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
2 ALABAMA, SOUTHERN 26392.000000 3 ALS ALABAMA, SOUTHERN 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
3 ARIZONA 109178.291667 5 AZ ARIZONA 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
4 ARKANSAS, EASTERN 33835.000000 6 ARE ARKANSAS, EASTERN 28875.088 20.282134 7.391100 8 1.119305e+11 2.444057e+06 POLYGON ((1245497.465320354 4071327.816416644,...
In [111]:
p.set_index('Jurisdiction', inplace=True)
p.head()
Out[111]:
Cost FID ABBR DISTRICT SQ_Miles Shape_Leng Shape_Area District_N Shape__Are Shape__Len geometry
Jurisdiction
ALABAMA, MIDDLE 269859.000000 1 ALM ALABAMA, MIDDLE 15571.036 9.918902 3.851034 11 5.634653e+10 1.200787e+06 POLYGON ((1773231.512352986 3791067.418154876,...
ALABAMA, NORTHERN 67802.000000 2 ALN ALABAMA, NORTHERN 22792.411 12.526683 5.752874 11 8.590670e+10 1.525150e+06 POLYGON ((1489037.117957535 3927953.039866449,...
ALABAMA, SOUTHERN 26392.000000 3 ALS ALABAMA, SOUTHERN 36728.698 14.291408 3.267182 11 4.755904e+10 1.725692e+06 (POLYGON ((1556659.714021636 3390941.689811857...
ARIZONA 109178.291667 5 AZ ARIZONA 114059.824 23.752602 28.930993 9 4.342576e+11 2.888358e+06 POLYGON ((-528566.6197332963 4158168.673864674...
ARKANSAS, EASTERN 33835.000000 6 ARE ARKANSAS, EASTERN 28875.088 20.282134 7.391100 8 1.119305e+11 2.444057e+06 POLYGON ((1245497.465320354 4071327.816416644,...
In [112]:
p.info()
<class 'pandas.core.frame.DataFrame'>
Index: 81 entries, ALABAMA, MIDDLE to WISCONSIN, WESTERN
Data columns (total 11 columns):
Cost          81 non-null float64
FID           81 non-null int64
ABBR          81 non-null object
DISTRICT      81 non-null object
SQ_Miles      81 non-null float64
Shape_Leng    81 non-null float64
Shape_Area    81 non-null float64
District_N    81 non-null object
Shape__Are    81 non-null float64
Shape__Len    81 non-null float64
geometry      81 non-null object
dtypes: float64(6), int64(1), object(4)
memory usage: 7.6+ KB
In [113]:
p = gpd.GeoDataFrame(p)
In [114]:
    #Set up figure and axis with different size
    f, ax = plt.subplots(1, figsize=(15,15))
    #Remove axis
    ax.set_axis_off()
    #Set title 
    ax.set_title('Mean Cost of Wiretaps per Federal Jurisdiction, 2017', fontsize=25)
    #Add baselayer
    jur.plot(ax=ax, color='white', edgecolor='grey')
    #Add second layer of polygons on the axis
    p.plot(column='Cost', ax=ax, legend=False)
    #Add legend
    mn = p.Cost.min()
    mx = p.Cost.max()
    norm = Normalize(vmin=mn, vmax=mx)
    n_cmap = cm.ScalarMappable(norm=norm, cmap="viridis")
    n_cmap.set_array([])
    ax.get_figure().colorbar(n_cmap, ax=ax, orientation='vertical', shrink=.5)
    #Display
    plt.show()

Next Steps

b. Create time series map for that shows change over time per district for at least one variable.

b. Take a closer look at particular jurisdictions such as those in Southern California and Texas.

c. It could also be interesting to single out certain outliers that incurred extremely high costs or led to lots of arrests.

d. Who is getting a "return on their investment?" Who is "striking out?"

In [ ]: